NULLIF with COALESCE

I was writing a stored procedure to retrieve a bunch of columns from a table. Noticing that there were NULL values in it I tried using ISNULL in my select query,
SELECT ISNULL(ColumnName, ‘No value in column’) as ColumnName
the returned values were still null no change, then it dawned on me that maybe they are not null and are just empty.
So, I used COALESCE along with NULLIF and i replaced ‘No value in column’ with the empty values,
the syntax is as follows,
COALESCE(NULLIF(ColumnName,’ ‘), ‘No value assigned’) as ColumnName.
What does COALESCE do?
It returns the first non null values from its arguments. If all the values are null then the function returns NULL.
COALESCE(Column1, Column2, Column3,’not available’,’this is fun’) is equal to

CASE

WHEN (Column1 IS NOT NULL) THEN Column1
WHEN (Column2 IS NOT NULL) THEN Column2
WHEN (Column3 IS NOT NULL) THEN Column2
WHEN (‘not available’ IS NOT NULL) THEN ‘not available’
WHEN (‘this is fun’ IS NOT NULL) THEN ‘this is fun’
ELSE NULL

What does NULLIF do?
It returns first expression if the two expressions are not equal, if they are equal then it returns NULL. It is equivalent to CASE statement used when two expressions are equal then the returned value is null.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s