ISNUMERIC

  • jlennartz

    SSCommitted

    Points: 1574

    The discussion usually answer my questions but not this time. Doesn't "isnumeric(char(32)) as n" return 0? And if so wouldn't "select sum(n) from l" also equal 0? I guess I don't see how the sum of n got to 16 if n is alway 0?

    Thanks, Jerry

  • SQLRNNR

    SSC Guru

    Points: 281252

    Interesting question and great feedback from the discussion!!

    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

  • sknox

    SSChampion

    Points: 12294

    jlennartz (9/27/2010)


    The discussion usually answer my questions but not this time. Doesn't "isnumeric(char(32)) as n" return 0? And if so wouldn't "select sum(n) from l" also equal 0? I guess I don't see how the sum of n got to 16 if n is alway 0?

    Thanks, Jerry

    Jerry, the recursive CTE [i.e, "with l as ( .... )" ] creates a set of rows with the value being tested ranging from 32 to 127, so the code actually tests each character from char(32) (i.e, ' ') to char(127). There are sixteen characters in that range that can be convertible to one or more data types (usually money, as that type seems to have a very broad tolerance for conversion.) See my earlier post for a reference to recursive CTEs.

  • Rose Bud

    SSCrazy

    Points: 2971

    jlennartz (9/27/2010)


    I guess I don't see how the sum of n got to 16 if n is alway 0?

    Jerry, hopefully someone will post a tidy explanation of how a recursive CTE works. In the meantime, run this SQL to see the entire contents of the 'l' table:

    with l as (

    select 32 as i, char(32) as s, isnumeric(char(32)) as n

    union all

    select i+1, char(i+1), isnumeric(char(i+1)) from l where i < 127

    )

    select * from l

    That should help you begin to understand what's going on.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    jlennartz (9/27/2010)


    The discussion usually answer my questions but not this time. Doesn't "isnumeric(char(32)) as n" return 0? And if so wouldn't "select sum(n) from l" also equal 0? I guess I don't see how the sum of n got to 16 if n is alway 0?

    The start of the query is a "recursive CTE". It consists of an anchor query and a recursive query. The anchor query is:

    select 32 as i, char(32) as s, isnumeric(char(32)) as n

    This returns a single row, with columns 1 (integer value 32), s (char(1), value ' ' (the space character)), and column n (integer value 0, the result of ISNUMERIC(char(32))).

    The recursive query is:

    select i+1, char(i+1), isnumeric(char(i+1)) from l where i < 127

    Note that the FROM clause references the CTE itself. That makes the CTE recursive. On the first iteration, the FROM denotes the reqults of the anchor query. Since i = 32 in that row, the recursive part will now produce a similar row for the value i+1 (33). But then the recursing starts. The recursive part is evaluated again, with this new row as input, so now a row is produced that starts with i = 33 and generates column values for the value 34. This continues until the WHERE clause is no longer satisfied.

    [The actual execution plan will probably be more efficient than this!]

    The result is a table with 96 rows, with i ranging from 32 to 127 and s and n representing char(i) and isnumeri(char(i)) for each row.

    The outer query then sums those isnumeric values.


    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/

  • jlennartz

    SSCommitted

    Points: 1574

    Thank You sknox, wware and especially Hugo. With Hugo's explaination it all became clear.

    Thanks to all,

    Jerry

  • sknox

    SSChampion

    Points: 12294

    jlennartz (9/27/2010)


    ...With Hugo's explaination it all became clear.

    It generally does. 😀

  • Andrew G

    SSChampion

    Points: 12808

    Why does select i+1, char(i+1), isnumeric(char(i+1)) from l where i < 127 need an i+1 in each column?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    foxxo (9/27/2010)


    Why does select i+1, char(i+1), isnumeric(char(i+1)) from l where i < 127 need an i+1 in each column?

    If you use

    select i+1, char(i), isnumeric(char(i)) from l where i < 127

    Then you'd probably get the same results from the summation, but if you check the actual rows produced by the CTE, you'd see weird results. For example, when i = 80, you'd get the number 81 for the row with char(80) and isnumeric(char(80)).


    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/

  • hrvoje.piasevoli

    Ten Centuries

    Points: 1377

    78% correct answers?! Yeah, right.

    Great question and good points in discussion by Hugo. Pleasure to read.

    But, thanks to all who participated in it.

    Hrvoje

    Hrvoje Piasevoli

  • jts2013

    Hall of Fame

    Points: 3226

    A good question that has taught me two things:

    1. You can't trust SQL's ISNUMERIC function

    2. You can build a recursive CTE - although I'm no sure I would want to 😉

    If anyone else has examples of CTE's I bet they'd make for some tricky questions?

    Thanks

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    jts_2003 (9/29/2010)


    2. You can build a recursive CTE - although I'm no sure I would want to 😉

    Recursive CTE'sare an invaluable tool when working with hierarchic data (like employee/supervisor relations, parts built of parts built of parts, genealogy data, etc). There are some examples of this in Books Online.


    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/

  • Artur Komkov

    Mr or Mrs. 500

    Points: 519

    Very nice question. And one more time I see that 'isnumeric' works in a way which is far away from our expectations. Always used custom made functions instead.

  • Enigma475

    Ten Centuries

    Points: 1247

    Great question! Now that I know how "liberal" is numeric is, I will be more careful with it.

    Thanks!!!

Viewing 14 posts - 31 through 44 (of 44 total)

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