How to write IsNullOrEmpty method similar to ISNULL in sql server

Question

I want to have function in sql server similar to ISNULL() except it should check expression for null and empty also.

If first parameter is null or empty, returns second.

can someone help me?

Solution 1

You can use NULLIF and ISNULL:

SELECT ISNULL(NULLIF(@parameter, ''), @parameter2)

Solution 2

NULLIF will return NULL incase the two parameters passed are same. And ISNULL will return the second value if the first value is NULL.

DECLARE @Input varchar(10) =NULL;
--DECLARE @Input varchar(10) ='';
--DECLARE @Input varchar(10) ='Good';

SELECT ISNULL(NULLIF(@Input,''),'Input is null or blank')

Solution 3

You can use COALESCE:

Eg:

SELECT COALESCE(First,Second,Third,'')
From TableName

COALESCE will return the first non-null value in the list. Read more here

EDIT:

For checking against emptystring, you can use CASE:

SELECT CASE WHEN COALESCE(Field,'')='' THEN 'This is NULL or Empty' END