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.
