My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

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 Displayfrom ...

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:

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:

nullif(override,'')

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(' ','') ---- NULL

(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?

If you happen to be stuck on some archaic or esoteric DBMS that doesn't support NULLIF, or if you have so many layers of COALESCE and NULLIF that you can't see straight, you could also convert it to a CASE statement:

Ive also used it to validate params in batches. For example, if you take in a loan application and it has a Co-Borrower FirstName & LastName. Together the two params are not required, but if one is populated the other must be populated.

NULLIF is fantastic! Been using it for a few years myself (stumbled across this article looking for a way to do it in C#). It can get really really powerful when you combine it with isnull, coalesce and whatnot for combining strings or categorizing data. I think I've come to use that little thing more than most other SQL commands!