Problem with COALESCE function....

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

General SQL Server Forums

New to SQL Server Programming

Problem with COALESCE function....

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-02-03 : 22:39:52

Hi all

If you look at BOL for [COALESCE] function we have:

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression..is an expression of any type.

Return Types
Returns the same value as expression.

-------------------------------------------
Ok as it claimes , arguments can be of any type also the Return value, BUT when i execute the following command:

Print COALESCE('An String',12) -- just for example

and in generel when you have an string argument in this function you will receive the following error:

Syntax error converting the varchar value 'An String' to a column of data type int.

Could anyonel help me?
Thanks in advance.
Regards.

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-02-03 : 22:49:41

select coalesce(columnname, value) from tablename. if the column name is null it will show the value

Rahul

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-02-03 : 22:52:53

so for example ify ou have a column with "string value" lets say column FIRSTNAME Takes STRING, iF there is a null value in firstname u can display that with 'zarin'.. syntax like this ,, select coalesce(FIRSTNAME, 'zarin') from tablename,, now wherever in the row there is a null in that column it will display zarin, you have to make sure its a null and not a empty value.. hope it helps..
cheers

Rahul

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-02-03 : 22:53:22

let me know if you are still stuck

Rahul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 23:00:59

This is the right way in which COALESCE works. It works the same way as CASE WHEN. As such it requires all its expressions to be of same datatype. change like this and see:-
Print COALESCE('An String','12')

While you can use ISNULL to work like this:-

Print ISNULL('An String',12)

But problem with ISNULL is the return datatype will same as type of first expression. See this:-

DECLARE @Str varchar(5)
Print ISNULL(@Str,'cdwfergthtyjukuikil')
this will return only first 5 characters as @str is of type varchar(5).

This is clearly suggested in BOL as:-

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

and look into definition of COALESCE which says:-

If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-02-03 : 23:57:26

Thanks
the point was what you mentioned...

quote:
[i]....it requires all its expressions to be of same datatype...

Thanks a million!!!.
Kind regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources