ISNUMERIC

  • sknox

    SSChampion

    Points: 12294

    Comments posted to this topic are about the item ISNUMERIC

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question.

    This should be good to help people understand how ISNUMERIC works...

  • Dhruvesh Shah

    SSCrazy

    Points: 2303

    Good Question I got lost in Union all.

  • subbu_e

    SSCrazy

    Points: 2431

    yes this really good question

  • tommyh

    SSCertifiable

    Points: 6252

    UMG Developer (9/25/2010)


    Thanks for the question.

    This should be good to help people understand how ISNUMERIC works...

    <rant>

    Yeah... like total crap. Its borderline useless. Just to further demonstrate that.

    select isnumeric('2e1')

    This gives 1! An 'e' isnt in my book numeric. Now i know SQL thinks its a calculator and thinks '2e1' = 20 (it does the same with a 'd' instead of the 'e'). But seriously, thats stupid.

    Even more stupid is that even though SQL thinks this is 20 you cant convert it to an Int. You can however convert it to a Float... then to an Int... sigh.

    .NET doesnt do this. Old VB6 does... but atleast VB accepts it as a Int so its consistant. SQL is divided and isnt sure abouts its decision if it really is numeric or not.

    </rant off>

    /T

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Good question, although I seem to remember that I've seen similar ones.

    But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.

    I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.

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

  • tommyh

    SSCertifiable

    Points: 6252

    da-zero (9/27/2010)


    Good question, although I seem to remember that I've seen similar ones.

    But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.

    I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.

    And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think.

    /T

  • paul.knibbs

    SSCoach

    Points: 15270

    da-zero (9/27/2010)


    Good question, although I seem to remember that I've seen similar ones.

    I'm sure there's been a very similar one in the past two or three months--I forget exactly when it was, though.

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    tommyh (9/27/2010)


    da-zero (9/27/2010)


    Good question, although I seem to remember that I've seen similar ones.

    But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.

    I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.

    And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think.

    /T

    How do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... :rolleyes:

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

  • tommyh

    SSCertifiable

    Points: 6252

    da-zero (9/27/2010)


    tommyh (9/27/2010)


    da-zero (9/27/2010)


    Good question, although I seem to remember that I've seen similar ones.

    But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.

    I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.

    And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think.

    /T

    How do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... :rolleyes:

    True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no.

    /T

  • Andrew G

    SSChampion

    Points: 12808

    Does the CTE with UNION ALL increment i? It needs to increment in each column? I no comprehende

  • Toreador

    SSChampion

    Points: 11261

    tommyh (9/27/2010)


    True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no.

    You might equally complain that '$1' should not count as numeric, because you may have meant to type '41', and '1$1' is not numeric so therefore that character should not be allowed anyway.

    While we're at it, '123' should not contain because you may have meant to type '124' 😉

  • firthr

    SSCommitted

    Points: 1606

    hmmm, I'm lost on the logic on this one, could some step through it please?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    tommyh (9/26/2010)


    UMG Developer (9/25/2010)


    Thanks for the question.

    This should be good to help people understand how ISNUMERIC works...

    <rant>

    Yeah... like total crap. Its borderline useless. Just to further demonstrate that.

    select isnumeric('2e1')

    This gives 1! An 'e' isnt in my book numeric. Now i know SQL thinks its a calculator and thinks '2e1' = 20 (it does the same with a 'd' instead of the 'e'). But seriously, thats stupid.

    ISNUMERIC is indeed useless (I even removed the "borderline" here). But not for the reasons you state. The reason it is unseles, is that it tells you if a string can be converted to "any" numerical data type, without bothering to tell you which data type. And since conversion rules are different for different types, a result of ISNUMER(...) = 1 does not guarantee a good conversion.

    SQL Server does not think it is a calculator. SQL Server recognises "2e1" as a valid form to input floating point data. The "2e1" is actually shorthand for "2 x 101". In this particular case, writing "20" would have been easier. But do you really prefer "0.0000000000000000000012" over "1.2e-21"? And do you prefer "123000000000000000000000000000000" over "1.23e32"? For those are values well within the data permitted range of floating point numbers.

    And another reason for writing "1e2" might be that you need the constant to be regarded as floating point data, to prevent conversions. Just as we sometimes type "3.0" instead of just "3" to be sure that SQL Server treates the constant as numeric and not as int. And just as we type "N'string'" instead of "'string'" to force unicode data.

    The "2e1" form is recognised by almost every programming language I have ever used. As is the "2d1" form (the difference is that one is double precision, the other normal precision - I don't know off the top of my head how and even if this translates to different data types in SQL Server, but at least for code compatbility, it recognises both forms).

    Like you, I think ISNUMERIC in its current form sucks. I really want Microsoft to give us a function that tells me if I can convert a specific string value in a specific data type. One that would permit a decimal point for conversion to numeric, but not for conversion to integer. And one that would permit "2e1" for conversion to float, but not to any other data type.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • tommyh

    SSCertifiable

    Points: 6252

    Toreador (9/27/2010)


    tommyh (9/27/2010)


    True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no.

    You might equally complain that '$1' should not count as numeric, because you may have meant to type '41', and '1$1' is not numeric so therefore that character should not be allowed anyway.

    While we're at it, '123' should not contain because you may have meant to type '124' 😉

    Yeah but i thought one exampel why this function sucks was enough.

    And yes i dont think $1 should count as numeric. $ is a currency symbol not a number. Its a property of a number but not part of the number itself (its not like that gets stored with the number anyway if you convert it to Int/Decimal... not even if you store it as money). In that case why not allow suffixes and we could really screw things up. Like 1' or 1K (both represent 1000).

    /T

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

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