IsNumeric with NULL and Empty String

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Comments posted to this topic are about the item IsNumeric with NULL and Empty String

    http://brittcluff.blogspot.com/

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice straight forward question - Thanks

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Data_God

    SSCrazy

    Points: 2720

    Whoo.Hooo..

    2nd comment on this QOTD. Haven't been able to do that in quite a while. Thanks for the good question. I got it right as I just recently had to deal with a very similar problem about how ISNULL works.

    Thanks again!!

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Data_God (8/3/2011)


    Whoo.Hooo..

    2nd comment on this QOTD. Haven't been able to do that in quite a while. Thanks for the good question. I got it right as I just recently had to deal with a very similar problem about how ISNULL works.

    Thanks again!!

    I think it is about implicit conversion

    Regards,

    Iulian

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thanks for the question

    Regards,

    Iulian

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question, thanks.

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

  • Ian_McCann

    SSCertifiable

    Points: 5132

    DOH!

    right answer, wrong reason.

    I saw the int, an empty string, an implicit conversion and an ISNULL.

    Went straight for the NO without reading the rest.

    If anyone feels they have ever been robbed of a point they are welcome to mine today.

    good question.

  • DugyC

    Hall of Fame

    Points: 3804

    Nice, straight forward, implicit conversion question. Greast start to the day.

    Cheers 😀

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Kwex

    Default port

    Points: 1407

    Nice question! I actually guessed on the basis of the first column returning a different result, not the second 🙂

  • M&M

    SSC-Insane

    Points: 21697

    Good question, thanks.

    M&M

  • This was removed by the editor as SPAM

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Looks like mostly good feedback. Glad most people have had a positive experience today.

    http://brittcluff.blogspot.com/

  • Thomas Abraham

    SSChampion

    Points: 10761

    Britt Cluff (8/4/2011)


    Looks like mostly good feedback. Glad most people have had a positive experience today.

    Wait for it. There will be someone that will hold a baby coyote in their left hand, a copy of a NYC subway map in their right, chant the names of the last 5 UN Secretary Generals, and manage to get a different result. 😉

    None of which will change my appreciation for a great question. Thanks. Count me as a "got it right for wrong reason" today. Good to be reminded of simple things like implicit conversion. I usually catch it in my own code, but maintain lots of code written by unseen developers.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • TomThomson

    SSC Guru

    Points: 104772

    Nice fun question.

    I imagine some people will get the right answer for the wrong reason, but at least they will not b emoaning about "I want my point". More people will get it right for the right reason. So perhaps the proportion of wrong answers will be rather smaller than usual.

    One potential gripe though: the reference given in the explanation doesn't explain what IsNumeric returns for NULL - but no-one should complain about that, as it probably isn't documented in BoL.

    I've always found it a bit disappointing that IsNumeric(Null) doesn't return NULL; after all, a NULL value doesn't contain any non-numeric characters, so it's not not numeric; and it doesn't contan any characters that are numeric, so it's not numeric either; so it should surely return NULL; just yet another case where (T-)SQL gets it wrong with NULL. But then IsNumeric is such an awful function anyway that that's teh least of its worries (perhaps the biggest is its name).

    Tom

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Implicit conversion is one of those issues, where if you know it, you just know. If you don't know it, the result is very unexpected. I am learning more and more of these, but I wonder, is there a singular document reference that lists all the implicit conversions that happen in SQL Server?

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

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