Coalesce and Conversion

Question

Post reply

Coalesce and Conversion

TomThomson

SSC Guru

Points: 104773
More actions
January 7, 2013 at 9:07 pm

#268470

Comments posted to this topic are about the item Coalesce and Conversion
Tom
January 8, 2013 at 8:25 am

This was removed by the editor as SPAM

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

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

demonfox SSCertifiable Points: 6289 More actions · Answer 1

I missed it completely 🙁

. no regret , learned something .

Also, I am glad to see that on aswering the question, equation for right and wrong is 0% and 100 %

Thanks for the question ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ralm Hall of Fame Points: 3156 More actions · Answer 3

Nice Question. Learnt something for today.

^{[font="Verdana"]Regards,
Rals[/font].}

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 4

still now i thought COALESCE will return the datatype of Non NUll value. Today only i learnt it will consider the highest percedence..

Thanks to author for posting nice qstn..

--
Dineshbabu
Desire to learn new things..

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Great and very interesting question. Thanks Tom!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22679 More actions · Answer 6

I prefer ISNULL, the cast is done to the first argument.

I use it e.g. with numeric type ISNULL(int_null,0). But, I should remember that with string ISNULL may be dangerous as in this case:

declare @C varchar(1)

select ISNULL(@c,'N/D')

Result:

N

instead of N/D

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 7

Thanks for the great question! A true learning question with no controversy.

Rune Bivrin SSCrazy Eights Points: 8522 More actions · Answer 8

Well, the question is a good one, and I got it right, but if I see code like that I reach for the shot gun.

Just because you're right doesn't mean everybody else is wrong.

mtassin SSC-Insane Points: 23099 More actions · Answer 9

Koen Verbeeck (1/8/2013)
Great and very interesting question. Thanks Tom!

+1 I must agree... was a lot of fun to sit there pondering if all of the various data types would be compatible with each other. Now where did I put that Ibuprofen? heh

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Good question Tom. Good to have a refresher on these things from time to time.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 11

Excellent question, and great explanation! Thanks!

Rob Schripsema
Propack, Inc.

john.arnott SSChampion Points: 11882 More actions · Answer 12

How annoying. You made me think! 😉

I agree this was an excellent question and explanation. I do see that one minor correction could be needed to one example given, however. You say "...for example the numeric value 37.2 can be converted to an int (37)...". Did you actually mean that an INT (@i) could be converted to the higher precedence numeric type, as obviously could be the literal value 37.2? After all, the result of that coalesce is 37.2 and not 37.

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

Revenant

SSC-Forever

Points: 42467

January 8, 2013 at 12:07 pm

#1574967

A really good QotD. Thanks, Tom!