COALESCE!?....[:0] - 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

General SQL Server Forums

New to SQL Server Programming

COALESCE!?....[:0]

Author

Topic

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 09:28:02

what the hell does that mean!?

how do i use it and where?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 09:30:07

Did you see it in BOL?

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 09:32:42

mr.madhivanan what exactly does BOL mean?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 09:34:17

Books On Line, SQL Server Help file
When you work in Query Analyser Press F1

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 09:37:52

oh so thats what it means! ya i did check it out but. the syntax and example they gave me doesnt seem to be clear enough. like their saying its similar to select-case but dont exactly know how the syntax works. can u give an example for both and what is the difference.

Imran,
"This summer, fear end and IK BEGINS!"

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 09:44:52

Silly word isn't it

COALESCE(A, B, C)

will give you the first parameter (i.e. A or B or C, in that order) this is NOT NULL. If all are NULL then you will get NULL.

There is also

ISNULL(A, b)

but this takes only two arguments, and is NOT standards compliant. And for me it's an even more silly word - functions called "IsXXX" should return true/false based on some test they perform ...

Kristen

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 10:39:03

quote:
Originally posted by Kristen
functions called "IsXXX" should return true/false based on some test they perform ...

Hehehe...yes Kristen, that was the source of many errors in my very
first attempts at SQL, on the lines of


IF ISNULL(@var)
...

HCL

"If it works fine, then it doesn't have enough features"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 10:39:49

>>IF ISNULL(@var)

Client Side coding

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-17 : 10:42:38

Yeah, particularly confusing since the ISNULL() function in VB does return a boolean check.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-17 : 11:00:17

I always felt the T-SQL function should be called IFNULL() instead of ISNULL() .... "Is" definitely implies that a boolean is returned. Plus, then it becomes a nice "opposite" of the little-used NULLIF().

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 11:03:20

"little-used NULLIF()"

Not little used here - what's wrong with

SELECT COALESCE(NullIf(MyColumn, ''), '0')

(Empty strings and NULL strings become '0' )

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-17 : 12:54:57

Not bad, Kristen. I use NULLIF() now and then as well, but you really don't see it too often.

Regarding your specific example, though, I would perhaps argue that if a single column in your SELECT is sometimes returning Nulls, sometimes empty strings, and sometimes numeric values you might have some issues ...

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 14:16:57

"you might have some issues"

Yup .. "Crap User Data" being presented from unvalidated sources

Plus my standard "Belt&Braces Data handler" logic!

Kristen

anuj164
Starting Member

49 Posts

Posted - 2005-10-17 : 16:43:47

smith

I personally NullIf() expression; it works great!!

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-10-18 : 11:33:59

I currently use LIKE for string comparision, can NullIf be used for the same purpose?

IF NullIf('string1','string2')

if the string1 and string2 are identical it will return NULL

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 11:58:16

Yes, there is no need to use LIKE if you don't require wildcards.

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-21 : 01:58:19

can u guys show an example for COALEASE and SELECT-CASE? it would be easier to understand.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-21 : 02:04:00

Is this?

Declare @t int
Select COALESCE(@t,0)
Select case when @t is null then 0 else @t end

Madhivanan

Failing to plan is Planning to fail