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
