Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Return The First Non Null Value

From Wiki

Jump to: navigation, search

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

  1. DECLARE @1 CHAR(1),@2 CHAR(1),@3 CHAR(1),@4 CHAR(1)
  2.     SELECT @4 ='D'
  3.  
  4.     SELECT COALESCE(@1,@2,@3,@4)
  5.     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.

  1. CREATE TABLE #Test1 (ID INT, SomeCol1 VARCHAR(20))
  2.     INSERT #Test1 VALUES(1,'1aaaaa')
  3.     INSERT #Test1 VALUES(2,'1bbbbb')
  4.     INSERT #Test1 VALUES(3,'1ccccc')
  5.     INSERT #Test1 VALUES(4,'1ddddd')
  6.  
  7.     CREATE TABLE #Test2 (ID INT, SomeCol2 VARCHAR(20))
  8.     INSERT #Test2 VALUES(1,'2aaaaa')
  9.     INSERT #Test2 VALUES(2,null)
  10.     INSERT #Test2 VALUES(3,'2ccccc')
  11.     INSERT #Test2 VALUES(4,null)
  12.  
  13.     SELECT COALESCE(SomeCol2,SomeCol1) AS SomeCol
  14.     FROM #Test1 t1
  15.     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

116 Rating: 1.0/5 (2 votes cast)