T-SQL COALESCE

Question

Post reply

T-SQL COALESCE

mjagadeeswari

SSC-Addicted

Points: 454
More actions
February 16, 2016 at 12:06 am

#308465

Comments posted to this topic are about the item T-SQL COALESCE
February 16, 2016 at 12:07 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

call.copse SSCoach Points: 18064 More actions · Answer 1

Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.

George Vobr SSChampion Points: 10009 More actions · Answer 2

Interesting question, thanks for excellent explanation and comprehensive reference.

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

Good question & explanation, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 4

call.copse (2/16/2016)
Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.

There wasnt any empty string trap, the second argument was ' ' not ''.

Maybe the second argument was intended to be '' to create such a trap ('' gives the same answer as '') and the space got in by accident.

Tom

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 5

Interesting to note that:

SELECT isnull (' ', GETDATE())

returns a blank string.

This is one of the reasons I don't like letting sql do implicit conversions for me.

Don Simpson

I'm not sure about Heisenberg.

Revenant SSC-Forever Points: 42467 More actions · Answer 6

DonlSimpson (2/16/2016)
Interesting to note that:
SELECT isnull (' ', GETDATE())
returns a blank string.
This is one of the reasons I don't like letting sql do implicit conversions for me.

The same. 🙂

And thanks to Jaga for the coffee break entertainment!

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Revenant (2/16/2016)
DonlSimpson (2/16/2016)
Interesting to note that:
SELECT isnull (' ', GETDATE())
returns a blank string.
This is one of the reasons I don't like letting sql do implicit conversions for me.
The same. 🙂
And thanks to Jaga for the coffee break entertainment!

There isn't any implicit conversion required there: ISNULL returns a value of the sanme type as its first parameter, but that doesn't result in any conversion unless the first parameter is NULL.

select isnull(cast(NULL as varchar(30)), getdate());

returned 'Feb 16 2016 8:07PM', a string with type varchar(30), a couple of minutes ago (WE Time zone here in Las Canarias).

I checked the type by using select isnull(cast(NULL as varchar(30)), getdate()) X into silly from tally where I=1 to create a table (silly) and looking at the single column's type.

I agree that implicit conversion is often undesirable; but sometimes it's quite useful. The implicit conversion done (if neeed) by ISNULL is a lot less confusing that the implicit conversion done by COALESCE because it desn't have the complication of choosing amongst a bunch of types using type precedence.

And it's a nice little question, that requires one to know the precedence rule.

Tom

starunit SSCommitted Points: 1817 More actions · Answer 8

Yup. Got burned by that back in '02.

Mark
Just a cog in the wheel.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 9

Nice question. A very good reminder. Thanks for sharing.

mjagadeeswari SSC-Addicted Points: 454 More actions · Answer 10

TomThomson (2/16/2016)
call.copse (2/16/2016)
Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.
There wasnt any empty string trap, the second argument was ' ' not ''.
Maybe the second argument was intended to be '' to create such a trap ('' gives the same answer as '') and the space got in by accident.

Had checked earlier before posting the ques what is the difference ..string with or without space? got same result on conversion, and i decided to go for string with a single space in the ques. Hehe. Trap.:-P

mjagadeeswari SSC-Addicted Points: 454 More actions · Answer 11

Revenant (2/16/2016)
DonlSimpson (2/16/2016)
Interesting to note that:
SELECT isnull (' ', GETDATE())
returns a blank string.
This is one of the reasons I don't like letting sql do implicit conversions for me.
The same. 🙂
And thanks to Jaga for the coffee break entertainment!

:satisfied:

mjagadeeswari SSC-Addicted Points: 454 More actions · Answer 12

TomThomson (2/16/2016)
Revenant (2/16/2016)
DonlSimpson (2/16/2016)
Interesting to note that:
SELECT isnull (' ', GETDATE())
returns a blank string.
This is one of the reasons I don't like letting sql do implicit conversions for me.
The same. 🙂
And thanks to Jaga for the coffee break entertainment!
There isn't any implicit conversion required there: ISNULL returns a value of the sanme type as its first parameter, but that doesn't result in any conversion unless the first parameter is NULL.
select isnull(cast(NULL as varchar(30)), getdate());
returned 'Feb 16 2016 8:07PM', a string with type varchar(30), a couple of minutes ago (WE Time zone here in Las Canarias).
I checked the type by using select isnull(cast(NULL as varchar(30)), getdate()) X into silly from tally where I=1 to create a table (silly) and looking at the single column's type.
I agree that implicit conversion is often undesirable; but sometimes it's quite useful. The implicit conversion done (if neeed) by ISNULL is a lot less confusing that the implicit conversion done by COALESCE because it desn't have the complication of choosing amongst a bunch of types using type precedence.
And it's a nice little question, that requires one to know the precedence rule.

Noted Tom. Thank you so much. yes, main thing is to know precedence rule here.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

akljfhnlaflkj

SSC Guru

Points: 76202

March 8, 2016 at 5:02 pm

#1863883

Thanks for the question.