A web application I have inherited uses data from a stored procedure that returns two columns: Description and Override. Basically, for each item returned, the web page should display the Description unless an Override is provided, in which case the Override is displayed. This appears to be a simple case of using ISNULL() or COALESCE():
select coalesce(Override, Description) as Display
Unfortunately, it turns out that there is a mixture of NULL values and empty strings ('') in the Override column, so this doesn't work -- that expression will return '' instead of using the override because the coalesce() check for NULL fails. So, on the surface, it seems that perhaps the only way to handle this is with a case expression:
select case when coalesce(Override, '') = '' then Description else Override end as Display
However, this is actually a great example of when the little-used NULLIF()
function can be handy. NULLIF() returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned. Seems a little odd and not very useful, but it is a great way of ensuring that empty strings are always returned as NULLS.
For example, the expression:
will never return an empty string; it will only return either a NULL value or a string with at least one character present. Also remember that SQL ignores trailing spaces when comparing strings, so even if the string isn't empty but it contains all spaces, it will still return NULL. You can see that behavior here:
select nullif(' ','')
(1 row(s) affected)
So, I was able to use NULLIF() along with COALESCE() to do the job in my scenario, without the need for a case, like this:
select coalesce(nullif(override,''), description) as Display
NULLIF() can be a handy function to employ now and then, even though on the surface it might not seem too useful. Be sure to consider it when you need to replace default values other than just NULL when using ISNULL() or COALESCE() expressions.
Does anyone else have any other handy uses for NULLIF() that you've come across in your experience?