Coalesce- Return 2nd Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Coalesce- Return 2nd Value

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-03-04 : 08:45:19

Hello,
Would there be a way to write a coalesce expression that returned the second non-null value rather then the first as in the following example:

COALESCE (column_M, column_B, column_K, column_F)

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-04 : 09:04:19

coalesce behavior is rigid in that sense. But, you can always go the long route which is,

case
   when column_M is not null and column_B is not null then column_B
   when column_B is not null and column_K is not null then column_K
   when column_K is not null and column_F is not null then column_F
   else column_F -- if that is what you want
end

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 09:06:51

quote:
Originally posted by sunitabeck

coalesce behavior is rigid in that sense. But, you can always go the long route which is,
case
when column_M is not null and column_B is not null then column_B
when column_B is not null and column_K is not null then column_K
when column_K is not null and column_F is not null then column_F
else column_F -- if that is what you want
end

doesn't look right to me.

eevans
Starting Member

48 Posts

Posted - 2009-03-04 : 09:10:30

Actually, that does do what I want it to. Thanks sunitabeck!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-05 : 10:11:20

eevans, sakets_2000 is right, in that, if you had data like this:

column_M = 'abc'
column_B = null
column_K = 'xyz'
column_F = null

You probably want to get 'xyz' as the value returned. But the query I posted yesterday would return you null.

If that is an issue for you, the query will need to be revised.

PS: I specialize in posting half-baked solutions to esoteric problems :--)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources