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

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this? There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Published Tuesday, November 06, 2007 2:28 PM by Denis Gobo

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

 

Hugo Kornelis said:

Hi Denis,

One of the reasons that the REPLACE option should not be used, is that it doesn't act according to specs. It will not return NULL if the data *is* a certain value, but if it *containts* a certain value:

DECLARE @1 varchar(10)

SELECT @1 ='ABCDEFG'

SELECT REPLACE(@1,'D',NULL)

Also, it will only operate on string data types.

Best, Hugo

November 7, 2007 2:40 AM
 

Denis Gobo said:

Hugo,

You are absolutely right, I should have explained why replace should not be used

November 7, 2007 7:48 AM
 

Vern Rabe said:

Hugo, why do you say REPLACE doesn't act according to specs? I'm missing something.

Thanks

Vern

November 7, 2007 12:30 PM
 

Denis Gobo said:

Vern,

run the code which Hugo posted and you will see it returns NULL but it should not. Id does with one character but when the value is possibly greater than the value you are looking for it is not correct. Run this code which should make it clear

DECLARE @1 varchar(10)

SELECT @1 ='ABCDEFG'

SELECT REPLACE(@1,'D',NULL),REPLACE(@1,'ABCDEFG',NULL)

SELECT NULLIF(@1,'D'), NULLIF(@1,'ABCDEFG')

November 7, 2007 12:34 PM
 

Vern Rabe said:

According to BOL, REPLACE "Returns NULL if any one of the arguments is NULL", so it should return NULL. And that seems logical to me, since it's effectively a concatination, and a NULL concatenated with anything is NULL.

I don't understand how the NULLIFs relate.

Vern

November 7, 2007 12:55 PM
 

Vern Rabe said:

Duh, now I get it. I'll blame it on the cold I'm recovering from ;)

Vern

November 7, 2007 3:41 PM
 

Hugo Kornelis said:

Hi Vern,

>>Hugo, why do you say REPLACE doesn't act according to specs? I'm missing something.<<

To clarify, REPLACE acts exactly according to the specs for REPLACE as they are stated in Books Online. It does not do what was stated in the specs (or maybe I should say: requirements) for Denis' problem: to return NULL only if the input *is* (rather than contains) a specific value.

Best, Hugo

November 8, 2007 2:34 AM

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