ISNUMERIC

  • chgn01

    Hall of Fame

    Points: 3530

    Comments posted to this topic are about the item ISNUMERIC

    --------------------------------------
    ;-)β€œEverything has beauty, but not everyone sees it.” ― Confucius

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Again making the trick with length of the column.. Nice ..

    --
    Dineshbabu
    Desire to learn new things..

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good Question but still I have doubts in some cases like:

    SELECT ISNUMERIC('.+') -- Returns 0

    SELECT ISNUMERIC('+.') -- Returns 1

    SELECT ISNUMERIC (',\') -- Returns 0

    SELECT ISNUMERIC ('\,') -- Returns 1

    :ermm:

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Danny Ocean

    SSCertifiable

    Points: 6098

    +1 πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?

    But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.

    Another relevant part of the explanation is missing as well, as is demonstrated by kapil_kk:

    kapil_kk (4/7/2013)


    Good Question but still I have doubts in some cases like:

    SELECT ISNUMERIC('.+') -- Returns 0

    SELECT ISNUMERIC('+.') -- Returns 1

    SELECT ISNUMERIC (',\') -- Returns 0

    SELECT ISNUMERIC ('\,') -- Returns 1

    :ermm:

    I can help him to some extent, but not fully. I can explain the first two, and IO'm going to do that in reversed order.

    SELECT ISNUMERIC('+.') -- Returns 1

    That is because just . is a valid numeric (represents 0.0). A trailing zero after a decimal point can be left out - nobody is surprised by 3. being equal to 3.0. A leading zero is also optional, we all write 3.1 instead of 003.1, and we all have seen numbers starting with a dot, like .31 instead of 0.31. What is unusual is both zeroes being removed at the same time - but if the number is 0.0 and the leading and trailing zeroes are removed, all that's left is a dot.

    And a plus sign before a number is also valid. We hardly ever use it - we are used to starting the number with a minus sign for negative, and omitting the plus sign for positive numbers. But it is valid to include it.

    Combine 0.0 represented as . with a leading plus sign, and you get +.

    SELECT ISNUMERIC('.+') -- Returns 0

    After the above explanation, this one is a lot shorter. A plus sign is only valid at the first non-blank position. Just as a negative. We al,low -13.14, but we frown upon 3-1.14, 31-.14, 31.1-4, and 31.14- (though the latter is sometimes usesd by bankers and accountants).

    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.


    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/

  • kapil_kk

    SSC-Insane

    Points: 21316

    Hugo Kornelis (4/8/2013)


    An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?

    But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.

    Another relevant part of the explanation is missing as well, as is demonstrated by kapil_kk:

    kapil_kk (4/7/2013)


    Good Question but still I have doubts in some cases like:

    SELECT ISNUMERIC('.+') -- Returns 0

    SELECT ISNUMERIC('+.') -- Returns 1

    SELECT ISNUMERIC (',\') -- Returns 0

    SELECT ISNUMERIC ('\,') -- Returns 1

    :ermm:

    I can help him to some extent, but not fully. I can explain the first two, and IO'm going to do that in reversed order.

    SELECT ISNUMERIC('+.') -- Returns 1

    That is because just . is a valid numeric (represents 0.0). A trailing zero after a decimal point can be left out - nobody is surprised by 3. being equal to 3.0. A leading zero is also optional, we all write 3.1 instead of 003.1, and we all have seen numbers starting with a dot, like .31 instead of 0.31. What is unusual is both zeroes being removed at the same time - but if the number is 0.0 and the leading and trailing zeroes are removed, all that's left is a dot.

    And a plus sign before a number is also valid. We hardly ever use it - we are used to starting the number with a minus sign for negative, and omitting the plus sign for positive numbers. But it is valid to include it.

    Combine 0.0 represented as . with a leading plus sign, and you get +.

    SELECT ISNUMERIC('.+') -- Returns 0

    After the above explanation, this one is a lot shorter. A plus sign is only valid at the first non-blank position. Just as a negative. We al,low -13.14, but we frown upon 3-1.14, 31-.14, 31.1-4, and 31.14- (though the latter is sometimes usesd by bankers and accountants).

    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.

    Thanks Hugo for the explanation πŸ™‚

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

    I am again surprised with these two statements behavior:

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

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

    I hope someone can explain to us about these two statements behavior.

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    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/

  • kapil_kk

    SSC-Insane

    Points: 21316

    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 :-):-)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • nenad-zivkovic

    Default port

    Points: 1446

    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.

    I've managed to find this info:

    http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx

    The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.

    http://blogs.msdn.com/b/michkap/archive/2005/10/12/479561.aspx

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    nenad-zivkovic (4/8/2013)


    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.

    I've managed to find this info:

    http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx

    The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.

    Ah thanks, that helps. I followed the link and read the surrounding text. Apparently, the backslash can be used as a currency symbol in Japanese and Korean languages. The link you supplied describes a behaviour change, where until SQL Server 2000 that backslash was only accepted if you actually had such a language as an active language, but since SQL Server 2005 the backslash is considered a valid currency symbol regardless of language setting.

    And of course, it makes sense that if you consider the backslash to be a valid currency symbol, it is valid at the start of a string when converting to money or smallmoney.

    EDIT: Corrected mistake with quote / end-quote tags


    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/

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Hugo Kornelis (4/8/2013)


    ....

    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.

    possibly can be this...

    http://www.tru64unix.compaq.com/docs/base_doc/DOCUMENTATION/V40F_HTML/AQTLTBTE/DOCU_010.HTM

    (scroll to the section - 1.8.3.4 Numeric Escape Sequences)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • angad.singh88

    Old Hand

    Points: 300

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

    Thanks to Hugo for his explanation.

    Angad

    --Angad Singh
    If I Stop Learning, I Cease to Be A SIKH !

  • TomThomson

    SSC Guru

    Points: 104772

    Hugo Kornelis (4/8/2013)


    nenad-zivkovic (4/8/2013)


    I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.

    I've managed to find this info:

    http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx

    The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.

    Ah thanks, that helps. I followed the link and read the surrounding text. Apparently, the backslash can be used as a currency symbol in Japanese and Korean languages.

    I don't think backslash really is a valid symbol for Yen or Won. It got used for Yen (and Yuan Renminbi, as that's the same symbol) because someone picked an already occupied 7-bit code point, and presumably was used for Won someone also picked that same codepoint for Won. It certainly wasn't used because any Korean or Japanese (or Chinese) person though that "\" was an appropriate symbol for their currency.

    Anyway, IBM and its asian customers seemed to regard it as a problem: see for example this or this. I recall also a recomendation to switch to a different code page in some circumstances on some systems to allow currency to be displayed correctly (with the Yen/Yuan Renminbi symbol, not backslash) and I imagine there was the same sort of recommendation for Won. So perhaps the idea that \ is a valid currency symbol doesn't have much support, except in the imagination of some people who have got accustomed do using the wrong currency symbol.

    Anyway, with the introduction of code page Windows 1252 there was no longer any excuse for using \ for Yen, because that had a separate code point A5 instead of backclash's 5C (Windows 1252 has twice as many code points as the original ascii). It's a pity there was no Windows 1252 code point for Won.

    edit: Just to add to the story of isnumeric's lunacy: isnumeric(nchar(8361)) delivers 0: the Won symbol is not numeric. But isnumeric(nchar(165)) delieves 1; the Yen symbol is numeric. Go figure :ermm:.

    Tom

  • mtassin

    SSC-Insane

    Points: 23096

    Hugo Kornelis (4/8/2013)


    An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?

    But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.

    I totally agree... my initial thought was all 0's.. because of NAME, but I then realized it was too simple, and went back.

    However, this again stops being as much about ISNUMERIC and more about truncation... *sigh*



    --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]

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

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