February 16, 2016 at 12:06 am
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
February 16, 2016 at 3:20 am
Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.
February 16, 2016 at 6:08 am
Interesting question, thanks for excellent explanation and comprehensive reference.
February 16, 2016 at 7:20 am
Good question & explanation, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 16, 2016 at 8:30 am
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
February 16, 2016 at 8:35 am
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
February 16, 2016 at 12:00 pm
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!
February 16, 2016 at 1:32 pm
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
February 16, 2016 at 1:56 pm
Yup. Got burned by that back in '02.
Mark
Just a cog in the wheel.
February 16, 2016 at 10:59 pm
Nice question. A very good reminder. Thanks for sharing.
February 22, 2016 at 3:49 am
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
February 22, 2016 at 3:50 am
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:
February 22, 2016 at 3:52 am
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.
March 8, 2016 at 5:02 pm
Thanks for the question.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply