Post reply

Numbers

  • January 4, 2012 at 9:24 am

    #1428426

    mohammed moinudheen (1/4/2012)

    Good question but I couldn't find pound symbol on my keyboard.

    Tsk, tsk... 😉

  • January 4, 2012 at 9:43 am

    #1428451

    Hugo Kornelis (1/4/2012)

    Thanks for the question, Stuart!

    The dollar and pound sign are valid in conversion from string to money or smallmoney (normally followed by the actual amount); the dot is valid in conversion from string to any non-integer data type (normally preceded by the whole part and followed by the fractional part). Without the numbers, the conversion will result, as Stuart already wrote, in the value zero.

    Hugo,

    Thanks for the clarification, but the following case does not seem to match your explanation, (or have I misunderstood?)

    SELECT CAST( '.' AS numeric)

    fails with 'Error converting...'

    Whereas both

    SELECT CAST( '.0' AS numeric)

    and

    SELECT CAST( '0.' AS numeric)

    work fine

    Nigel
    How to post questions

  • January 4, 2012 at 10:28 am

    #1428510

    The key phrase was "conversion from string to money or smallmoney"

    select cast('.' as money)

  • January 4, 2012 at 10:30 am

    #1428516

    good question!!!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • January 4, 2012 at 10:36 am

    #1428523

    nigel. (1/4/2012)

    Thanks for the clarification, but the following case does not seem to match your explanation, (or have I misunderstood?)

    SELECT CAST( '.' AS numeric)

    fails with 'Error converting...'

    That surprises me! (Yes, I did not test all cases)

    As Torreador already posted, just the dot can be converted to money and smallmoney, which is sufficient for ISNUMERIC to return 1.

    But it doesn't make any sense at all. Why is conversion of '.' to money and smallmoney allowed, but not to decimal, numeric, or float? Especially since both '.0' and '0.' are valid in conversions to ALL these types? There is no logic in this!

    Thanks for catching and pointing out this inaccuracy in my previous post!

    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/

  • January 4, 2012 at 10:40 am

    #1428526

    Hugo Kornelis (1/4/2012)There is no logic in this!

    describes the IsNumeric function in a nutshell 😉

  • January 4, 2012 at 10:53 am

    #1428535

    Here's another beauty.

    Select ISNUMERIC('£,,1.,') As "String";

    Still returns a 1 :crazy:

  • January 4, 2012 at 11:19 am

    #1428551

    Dave62 (1/4/2012)

    Here's another beauty.

    Select ISNUMERIC('£,,1.,') As "String";

    Still returns a 1 :crazy:

    It was a couple of real life examples like this (when importing a csv) that gave me the idea for this QOTD

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • January 4, 2012 at 11:46 am

    #1428563

    Stuart Davies (1/4/2012)

    ...

    It was a couple of real life examples like this (when importing a csv) that gave me the idea for this QOTD

    This one even works with no number at all.

    Select ISNUMERIC('£,,.,') As "String";

    So now I'm wondering if there is a better option that should be used in place of or in combination with ISNUMERIC?

  • January 4, 2012 at 1:03 pm

    #1428619

    This is really good to know. I will keep this in mind.

  • January 4, 2012 at 10:09 pm

    #1428789

    The weirdness of ISNUMERIC is just one more reason I am looking forward to SQL Server 2012:

    TRY_CONVERT

    TRY_PARSE

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 4, 2012 at 10:37 pm

    #1428791

    SQL Kiwi (1/4/2012)

    The weirdness of ISNUMERIC is just one more reason I am looking forward to SQL Server 2012:

    TRY_CONVERT

    TRY_PARSE

    ... coming on March 23rd

  • January 5, 2012 at 12:33 am

    #1428808

    Revenant (1/4/2012)

    SQL Kiwi (1/4/2012)

    The weirdness of ISNUMERIC is just one more reason I am looking forward to SQL Server 2012:

    TRY_CONVERT

    TRY_PARSE

    ... coming on March 23rd

    Is that official? I couldn't find anything on a quick search.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 5, 2012 at 3:17 am

    #1428863

    Stuart Davies (1/4/2012)

    Dave62 (1/4/2012)

    Here's another beauty.

    Select ISNUMERIC('£,,1.,') As "String";

    Still returns a 1 :crazy:

    Based on Jeff Moden's SQL Spackle on ISNUMERIC[/url], any of these characters evaluates to 1; so I'd assume that even a combination of "valid ISNUMERIC characters" results as 1.

    Adding a single non-ISNUMERIC character to the string will return 0 as result:

    select ISNUMERIC('£,,1.,a') col1;

    It was a couple of real life examples like this (when importing a csv) that gave me the idea for this QOTD

    Glad I'm usually working in a German context--the field delimiter in a .CSV or .TXT file has to be a semicolon (;) which will evaluate to 0. One of the rare occassions where it's actually a benefit... ;-):cool:

    -Michael

  • January 16, 2012 at 8:15 pm

    #1434379

    bitbucket-25253 (1/3/2012)

    Thank goodness for Jeff Moden's writing in the series of articles called "SPACKLE"

    Speakiing of thos short but SO INFORMATIVE ARTICLES, what happend to the entry in the left most frame linking to that series of articles?

    You're too kind, Ron. There never was such a link in the frame you're talking about.

    Shifting gears, considering the article you're talking about, it's probably a good thing I got this question right, huh? 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 34 total)

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