Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

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.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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

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:

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

Feedback

Gravatar

# 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
9/27/2007 10:00 AM | Peter Larsson
Gravatar

# 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!
9/27/2007 10:59 AM | georgev
Gravatar

# 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!
9/27/2007 11:04 AM | georgev
Gravatar

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

Nice catch on both accounts, georgev !!
9/27/2007 11:28 AM | Jeff
Gravatar

# 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
9/27/2007 11:34 AM | Rick O
Gravatar

# 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 ...
9/27/2007 12:40 PM | Jeff
Gravatar

# 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
9/28/2007 5:35 AM | georgev
Gravatar

# 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
10/4/2007 9:11 AM | George P
Gravatar

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

Nice example George, thank you!
10/4/2007 10:45 AM | Jeff
Gravatar

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


11/1/2007 2:39 AM | Learner
Gravatar

# 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 ('').
11/1/2007 8:13 AM | Jeff
Gravatar

# 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
12/10/2007 9:41 PM | Nathan Skerl
Gravatar

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


12/10/2007 10:43 PM | Nathan Skerl
Gravatar

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

I'm using "nullif" function. I think realy useful.
12/25/2007 6:45 AM | Martyy
Gravatar

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

Great help , thanks
12/2/2008 10:39 AM | Ljs
Gravatar

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

nice article. Thanks Jeff!
12/3/2008 2:43 PM | Thoulfekar
Gravatar

# 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 :)
2/9/2009 6:17 AM | Charlie
Gravatar

# 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!
3/19/2009 2:53 PM | Usarian
Gravatar

# 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!
6/3/2009 3:54 AM | bin
Gravatar

# 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!
8/11/2009 8:22 PM | Jeff DeCew
Gravatar

# 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.
9/16/2009 2:55 PM | Paul
Gravatar

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

great article
6/9/2010 8:48 AM | daljit
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET