Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

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: 2.2/5 (10 votes cast)