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 The First Non Null Value
From Wiki
To return the first non null value from a bunch of values use ISNULL or COALESCE. If you need to check for more than 2 values then you will need to use COALESCE sicne ISNULL can only take two values.
Run this to see how it works
- DECLARE @1 CHAR(1),@2 CHAR(1),@3 CHAR(1),@4 CHAR(1)
- SELECT @4 ='D'
- SELECT COALESCE(@1,@2,@3,@4)
- SELECT ISNULL(@3,@4)
In real code you will probably have a join and check for work/home email or work/home phone number columns. Below is an example.
- CREATE TABLE #Test1 (ID INT, SomeCol1 VARCHAR(20))
- INSERT #Test1 VALUES(1,'1aaaaa')
- INSERT #Test1 VALUES(2,'1bbbbb')
- INSERT #Test1 VALUES(3,'1ccccc')
- INSERT #Test1 VALUES(4,'1ddddd')
- CREATE TABLE #Test2 (ID INT, SomeCol2 VARCHAR(20))
- INSERT #Test2 VALUES(1,'2aaaaa')
- INSERT #Test2 VALUES(2,null)
- INSERT #Test2 VALUES(3,'2ccccc')
- INSERT #Test2 VALUES(4,null)
- SELECT COALESCE(SomeCol2,SomeCol1) AS SomeCol
- FROM #Test1 t1
- JOIN #Test2 t2 ON t1.ID =t2.ID
| SomeCol |
| 2aaaaa |
| 1bbbbb |
| 2ccccc |
| 1ddddd |
As you can see two times the result came from the #Test2 table and two times from the #Test1 table
Contributed by: --SQLDenis 16:32, 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.