Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.