ISNUMERIC

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    I think the short takeaway is that IsNumeric just shouldn't be trusted. It looks like a best-practice way to check that a string is a number but it contains a hidden gotcha that you'll only learn about when the data contains the edge cases (fun to debug)

    I feel like this is the same class of [mis]behavior as the isnull() vs coalesce() trickery re data type

    It's a good question to raise the issue. It's unfortunate that these pit-traps lie in wait for unsuspecting users.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    As far as I understand, IsNumeric works roughly like this:

    CASE WHEN COALESCE(

    TRY_CAST(@input AS numeric)

    ,TRY_CAST(@input AS bigint)

    ,TRY_CAST(@input AS money)

    ,TRY_CAST(@input AS smallint)

    ,TRY_CAST(@input AS smallmoney)

    ,TRY_CAST(@input AS tinyint)

    ,TRY_CAST(@input AS float)

    ,TRY_CAST(@input AS decimal)

    ,TRY_CAST(@input AS real

    )

    ) IS NULL THEN 0 ELSE 1 END

    So as long as the conversion to any of the numeric data types works, it will return 1 otherwise 0.

    Best Regards,

    Chris Bรผttner

  • jpentz99

    Old Hand

    Points: 344

    Heh - I got burnt with ISNUMERIC just today - had some dodgy character data and thought it might just work to test which records I could convert to int, but it was a no go.

    The following article appeared a few months back for those who want to dig into ISNUMERIC some more: http://www.sqlservercentral.com/articles/IsNumeric/71512/

    It also has a handy way to check for just numbers in a string which is what I ended up using instead of ISNUMERIC.

  • sestell1

    SSChampion

    Points: 10230

    Christian Buettner-167247 (4/8/2013)


    As far as I understand, IsNumeric works roughly like this:

    CASE WHEN COALESCE(

    TRY_CAST(@input AS numeric)

    ,TRY_CAST(@input AS bigint)

    ,TRY_CAST(@input AS money)

    ,TRY_CAST(@input AS smallint)

    ,TRY_CAST(@input AS smallmoney)

    ,TRY_CAST(@input AS tinyint)

    ,TRY_CAST(@input AS float)

    ,TRY_CAST(@input AS decimal)

    ,TRY_CAST(@input AS real

    )

    ) IS NULL THEN 0 ELSE 1 END

    So as long as the conversion to any of the numeric data types works, it will return 1 otherwise 0.

    That is my understanding as well. Unfortunately it doesn't provide any feedback as to which conversion actually worked, which in my opinion makes it a rather useless function.

  • Revenant

    SSC-Forever

    Points: 42467

    angad.singh88 (4/8/2013)


    Thanks for the most obvious answer to turn out to be something not known. ๐Ÿ™‚

    Thanks to Hugo for his explanation.

    Angad

    +1, with thanks to Gary.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Revenant (4/8/2013)


    angad.singh88 (4/8/2013)


    Thanks for the most obvious answer to turn out to be something not known. ๐Ÿ™‚

    Thanks to Hugo for his explanation.

    Angad

    +1, with thanks to Gary.

    +1

    Thanks

  • manik_anu

    SSCrazy

    Points: 2367

    kapil_kk (4/8/2013)


    Hugo Kornelis (4/8/2013)


    kapil_kk (4/8/2013)


    For the first part I understand the behavior but am still confused with the SELECT ISNUMERIC('.+') -- Returns 0 behavior ๐Ÿ™

    The . actually represents 0.0, with leading and trailing zero omitted.

    So +. is equivalent to +0.0, which is a valid number. But .+ is equivalent to 0.0+, which is not. The plus sign (just as the minus sign) is only valid at the start of the numeric string.

    I am again surprised with these two statements behavior:

    SELECT ISNUMERIC('.,') -- Return 1

    SELECT ISNUMERIC(',.') -- Return 1

    The comma is used as the thousands seperator in America (and, as far as I know, most English speaking countries). When converting from string to money and smallmoney, thousands seperators in the string are accepted. This is implemented in a rather loose way - there is no checkk if the thousands seperators are where they should be, they are simply ignored.

    Thanks Hugo, now its clear to me :-):-)

    +1... nice explanation...

    Manik
    You cannot get to the top by sitting on your bottom.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    This is a funny QOTD.

    The only one I didn't get was the 9th.

    Is \, numeric ?

Viewing 8 posts - 16 through 23 (of 23 total)

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