Jeff's SQL Server Blog

A handy but little-known SQL function: NULLIF()

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():


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:


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:


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:


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?

see also:

• Some SELECTs will never return 0 rows -- regardless of the criteria
• By The Way ... DISTINCT is not a function ...
• A handy but little-known SQL function: NULLIF()
• Is it a String Literal or an Alias?
• Passing an Array or Table Parameter to a Stored Procedure
• SELECT * FROM TABLE -- except for these columns
• In SQL, it's a Case Expression, *not* a Case Statement
• Returning Random Numbers in a SELECT statement

Print | posted on Thursday, September 27, 2007 9:50 AM | Filed Under [ T-SQL ]