COALESCE FUNCTION?? REPLACEMENT

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 FUNCTION?? REPLACEMENT

Author

Topic

reymon26
Starting Member

7 Posts

Posted - 2010-11-25 : 07:40:30

is there an updated approach on how to use the COALESCE keyword?

Or if there is a NEW keyword that will replace COALESCE on SQL 2005/2003?

thank you

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-25 : 07:41:40

Coalesce is there in 2005 and 2008 (no such thing as SQL 2003). Hasn't changed, not removed.

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 07:42:45

>> is there an updated approach on how to use the COALESCE keyword?
What do you mean?
It did change I believe so that it can return null - not sure when that was but long ago.

Or if there is a NEW keyword that will replace COALESCE on SQL 2005/2003?
No and what's sql2003?
coalesce is ansi standard.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

reymon26
Starting Member

7 Posts

Posted - 2010-11-25 : 08:09:45

thanks for the info, do you have an idea what function can be use as the same function of coalesce?

quote:
Originally posted by GilaMonster

Coalesce is there in 2005 and 2008 (no such thing as SQL 2003). Hasn't changed, not removed.

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 08:20:59

nested isnull/case statement?
Oracle I think can use a decode to do a similar thing.

What's wrong with coalesce?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-25 : 08:21:33

quote:
Originally posted by reymon26

thanks for the info, do you have an idea what function can be use as the same function of coalesce?

quote:
Originally posted by GilaMonster

Coalesce is there in 2005 and 2008 (no such thing as SQL 2003). Hasn't changed, not removed.

--
Gail Shaw
SQL Server MVP

You mean in place of Coalesce. Then you can use IsNull for each value to be checked but this approach increased line of code and not preferred.

Example Isnull(null , Isnull('Not Null now','Passed'))

Kristen
Test

22859 Posts

Posted - 2010-11-25 : 11:56:05

IsNull() also has issues with parameter datatype implicit conversion. Not saying COALESCE hasn't, but COALESCE is probably more what people are expecting for implicit conversion.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 09:39:58

quote:
Originally posted by Kristen

IsNull() also has issues with parameter datatype implicit conversion. Not saying COALESCE hasn't, but COALESCE is probably more what people are expecting for implicit conversion.

Also the name isnull() informs that it should return 0 or 1 like isdate()