Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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

by Jeff Smith in  

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

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

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?

see also:

Legacy Comments
Peter Larsson
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
Great example!

But... What if description also can contain NULL or empty space?

select coalesce(nullif(override,''), nullif(description, '', '<nothing to see>') as Display
georgev
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
I think Peter means

SELECT Coalesce(NullIf(override,''), Coalesce(NullIf(description,''),'nothing to see')) As ':-)'

Lovely article Jeff - I've been looking for a function like this for a verrrrry long time!
georgev
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
One quick amendment to my... amendment. I hope it is spotted ;)

SELECT Coalesce(NullIf(override,''), Coalesce(NullIf(description,''),'nothing to see')) As [:-)]

Still trying to break that old habit!
Jeff
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
Nice catch on both accounts, georgev !!
Rick O
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
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:

SELECT COALESCE(CASE WHEN override <> '' THEN override END, description) AS display

-R
Jeff
2007-09-27		 re: A handy but little-known T-SQL function: NULLIF()
Thanks, Rick -- I actually already showed how to do it w/o using NULLIF() in the very beginning of the post ...
georgev
2007-09-28		 re: A handy but little-known SQL function: NULLIF()
If you really don't have the NULLIF() function then give this UDF a whirl

IF EXISTS (SELECT 1 FROM sysobjects WHERE type='FN' AND name='IfNull') BEGIN
DROP FUNCTION IfNull
END
GO

CREATE FUNCTION IfNull (
@value varchar(256)
)
RETURNS varchar(256)
AS
BEGIN
DECLARE @return varchar(256)
SET @return =
CASE Coalesce(@value,'')
WHEN '' THEN NULL
ELSE @value
END
RETURN @return
END
GO

SELECT dbo.IfNull('123') As [text], dbo.IfNull('') As [blank], dbo.IfNull(NULL) As [NULL], dbo.IfNull(' ') As [white space]
GO

DROP FUNCTION IfNull
George P
2007-10-04		 re: A handy but little-known SQL function: NULLIF()
Another good use for NULLIF is to prevent 'Division by 0' errors:

--will raise an error
select a / b
from (
select 1 as a, 1 as b union all
select 1, 0
) t

--works - will return NULL if the denominator is 0
select a / nullif (b, 0)
from (
select 1 as a, 1 as b union all
select 1, 0
) t

So you don't have to worry anymore when you deal with 0's
Jeff
2007-10-04		 re: A handy but little-known SQL function: NULLIF()
Nice example George, thank you!
Learner
2007-11-01		 re: A handy but little-known SQL function: NULLIF()
I tried with some values of override and description as columns in a table. It displayed value of description even when value of override is empty string.


select coalesce(Override, Description) as Display................

It displays value of description when value of override is either null or EMPTY STRING.
Jeff
2007-11-01		 re: A handy but little-known SQL function: NULLIF()
Learner -- no, it doesn't. Are you sure you didn't have NULLs in there? You should always test with a small, specific sample. Run this and let me know what it returns:

select coalesce('','default')

According to you, it should return 'default', but it doesn't. It returns the empty string ('').
Nathan Skerl
2007-12-10		 re: A handy but little-known SQL function: NULLIF()
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.

There are many ways to achieve this, this is one:

declare @A char(1),
@B char(1)

set @A = 'X'
set @B = ''

if ( nullif( isnull(@A, ''), '') +
nullif( isnull(@B, ''), '')
) is null
begin
print 'Failure'
end else
begin
print 'Success'
end
Nathan Skerl
2007-12-10		 re: A handy but little-known SQL function: NULLIF()
Actually... thats not going to (now that ive actually run that psuedo-code).

Now im curious how could we use NULLIF to validate params in batches. If any one of a collection of params is populated then all of them must be populated. Otherwise, all must be null.
Martyy
2007-12-25		 re: A handy but little-known SQL function: NULLIF()
I'm using "nullif" function. I think realy useful.
Ljs
2008-12-02		 re: A handy but little-known SQL function: NULLIF()
Great help , thanks
Thoulfekar
2008-12-03		 re: A handy but little-known SQL function: NULLIF()
nice article. Thanks Jeff!
Charlie
2009-02-09		 re: A handy but little-known SQL function: NULLIF()
Really useful, can't believe I have be SQL'ing all this time and not come across this before :)
Usarian
2009-03-19		 re: A handy but little-known SQL function: NULLIF()
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!
bin
2009-06-03		 re: A handy but little-known SQL function: NULLIF()
thank you very much for this post :) I didn't know this function even I work with SQL since 6 years :) Thanks! Very helpful!
Jeff DeCew
2009-08-11		 re: A handy but little-known SQL function: NULLIF()
NULLIF() can also be really useful in COUNT() expressions. Count works by incrementing for every non-null value.

Let's say you have a table Animals, which has 3 columns: `name`, `isPet`, `isDog`
Therefore, let's say you want to know how many of your animals are pets and how many are dogs.

SELECT count(*) AS total, count(nullif(isPet,0)) AS numPets, count(nullif(isDog,0)) AS numDogs FROM Animals

That gets you 3 different counts with a single query!
Paul
2009-09-16		 re: A handy but little-known SQL function: NULLIF()
Nice Query ! how can we take out only numeric valuefrom column which datatype is nvarchar using NULLIF

Col1
12
34
1238489
null
null
7483
-8236
yes
0.67346346
23.8723477348348
na
no
23

Desired result

12.00
34.00
1238489.00
7483.00
-8236.00
0.67
23.87
23.00

Thank you for taking your timeout for considering this issue.
daljit
2010-06-09		 re: A handy but little-known SQL function: NULLIF()
great article