Return Null If A Value Is A Certain Value
From Wiki
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
Contributed by: --SQLDenis 16:34, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section NULLS
