THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

The Strange Case OF Nulls And Case

Okay it is not Dr. Jekyll and Mr. Hyde but I had to come up with a title. This was a question someone asked yesterday in the sql programming group


http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/126735827b7ae667#

This person had a case statement like this

SELECT

CASE

WHEN COL1 IS NULL THEN 'UNK'

WHEN COL1 = 'SUGAR' THEN 'SGR'

WHEN COL1 = 'SALT' THEN 'SAL'

WHEN COL1 = 'PEPPER' THEN 'PEP'

END AS items

FROM #INV_ITEMS

This still returned NULLS. Can you spot the flaw? There is no else statement, if there is a value which is not sugar, salt or pepper then a NULL will be returned. let's take a look.

Create this table

CREATE TABLE #INV_ITEMS (COL1 varchar(23))

INSERT #INV_ITEMS VALUES('SUGAR')

INSERT #INV_ITEMS VALUES('SALT')

INSERT #INV_ITEMS VALUES('PEPPER')

INSERT #INV_ITEMS VALUES('WASABI')

 

Now run this

SELECT

CASE

WHEN COL1 IS NULL THEN 'UNK'

WHEN COL1 = 'SUGAR' THEN 'SGR'

WHEN COL1 = 'SALT' THEN 'SAL'

WHEN COL1 = 'PEPPER' THEN 'PEP'

END AS items

FROM #INV_ITEMS

Output
--------
SGR
SAL
PEP
NULL


So we get a NULL, but which row is that? We can just add the column to see the original value

 

SELECT

COL1,CASE

WHEN COL1 IS NULL THEN 'UNK'

WHEN COL1 = 'SUGAR' THEN 'SGR'

WHEN COL1 = 'SALT' THEN 'SAL'

WHEN COL1 = 'PEPPER' THEN 'PEP'

END AS items

FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI NULL

Aha, it is the wasabi.

Let's try again by including an ELSE

SELECT

COL1,CASE

WHEN COL1 IS NULL THEN 'UNK'

WHEN COL1 = 'SUGAR' THEN 'SGR'

WHEN COL1 = 'SALT' THEN 'SAL'

WHEN COL1 = 'PEPPER' THEN 'PEP'

ELSE 'UNK'

END AS items

FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK

There we go, it is correct now.

Now let's make it more interesting by inserting a blank, a space and a NULL


INSERT #INV_ITEMS VALUES('')

INSERT #INV_ITEMS VALUES(' ')

INSERT #INV_ITEMS VALUES(NULL)

 

We can use a combination of NULLIF and RTRIM to filter out blanks, spaces and NULLs

SELECT

COL1,CASE

WHEN NULLIF(RTRIM(COL1),'') IS NULL THEN 'NullOrBlank'

WHEN COL1 = 'SUGAR' THEN 'SGR'

WHEN COL1 = 'SALT' THEN 'SAL'

WHEN COL1 = 'PEPPER' THEN 'PEP'

ELSE 'UNK'

END AS items

FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK
 NullOrBlank
    NullOrBlank
NULL NullOrBlank

And that is all

DROP TABLE #INV_ITEMS

Published Thursday, December 13, 2007 2:48 PM by Denis Gobo
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement