[tsql] How to check if a column is empty or null using SQL query select statement?

How do I check if a column is empty or null using a SQL select statement?

For instance, if I want to check:

select * from UserProfile WHERE PropertydefinitionID in (40, 53) and PropertyValue is null or empty

Here is my preferred way to check for "if null or empty":

SELECT * 
FROM UserProfile 
WHERE PropertydefinitionID in (40, 53) 
AND NULLIF(PropertyValue, '') is null

Since it modifies the search argument (SARG) it might have performance issues because it might not use an existing index on the PropertyValue column.

An answer above got me 99% of the way there (thanks Denis Ivin!). For my PHP / MySQL implementation, I needed to change the syntax a little:

SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
AND (LENGTH(IFNULL(PropertyValue,'')) = 0)

LEN becomes LENGTH and ISNULL becomes IFNULL.

select isnull(nullif(CAR_OWNER_TEL, ''), 'NULLLLL')  PHONE from TABLE

will replace CAR_OWNER_TEL if empty by NULLLLL (MS SQL)

Here's a slightly different way:

SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
  AND (LEN(ISNULL(PropertyValue,'')) = 0)

If you want blanks and NULLS to be displayed as other text, such as "Uncategorized" you can simply say...

SELECT ISNULL(NULLIF([PropertyValue], ''), 'Uncategorized') FROM UserProfile

The above answers the main question very well. This answer is an extension of that and is of value to readers.