COALESCE for NonEmpty

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  September 8, 2006 at 10:53 am

  #173929

  Is there a function to return the first nonempty value from a list of values ?

  Here is an example of a sql statement

  SELECT CASE

  WHEN ISNULL(ColumnA,'') <> '' THEN ColumnA

  WHEN ISNULL(ColumnB,'') <> '' THEN ColumnB

  WHEN ISNULL(ColumnC,'') <> '' THEN ColumnC

  WHEN ISNULL(ColumnD,'') <> '' THEN ColumnD

  END .....

  Is there a function that returns the first nonempty column from (ColumnA, ColumnB, ColumnC, ColumnD) ? The number of columns to be checked will not be constant.

  And the sql statement that I have is much more complex than this example.

   

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 8, 2006 at 11:16 am

  #659415

  I think this is what you are looking for

  select coalesce(nullif(ColumnA,''),nullif(ColumnB,''),nullif(ColumnC,''),nullif(ColumnD,'')) from tab

  But performance wise its too bad I guess

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 8, 2006 at 11:17 am

  #659416

  I don't think you'll find anything really fast in that query .