## ISNUMERIC

 Author Message jlennartz Ten Centuries Group: General Forum Members Points: 1150 Visits: 1197 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 Group: General Forum Members Points: 144491 Visits: 18651 Interesting question and great feedback from the discussion!! Jason...AKA CirqueDeSQLeil_______________________________________________I have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail ShawLearn Extended Events sknox SSCertifiable Group: General Forum Members Points: 6939 Visits: 3161 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, JerryJerry, 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 Group: General Forum Members Points: 2091 Visits: 1062 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 lThat should help you begin to understand what's going on. Hugo Kornelis SSC-Dedicated Group: General Forum Members Points: 34366 Visits: 13120 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: http://sqlblog.com/blogs/hugo_kornelis jlennartz Ten Centuries Group: General Forum Members Points: 1150 Visits: 1197 Thank You sknox, wware and especially Hugo. With Hugo's explaination it all became clear.Thanks to all,Jerry sknox SSCertifiable Group: General Forum Members Points: 6939 Visits: 3161 jlennartz (9/27/2010)...With Hugo's explaination it all became clear.It generally does. :-D Andrew G SSCertifiable Group: General Forum Members Points: 7927 Visits: 2345 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-Dedicated Group: General Forum Members Points: 34366 Visits: 13120 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: http://sqlblog.com/blogs/hugo_kornelis hrvoje.piasevoli SSC Eights! Group: General Forum Members Points: 905 Visits: 510 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