Return Null If A Value Is A Certain Value

Denis Gobo — Tue, 06 Nov 2007 18:28:00 GMT

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

Denis Gobo : Nulls, SQL Server 2008

Return Null If A Value Is A Certain Value