ISNUMERIC

Question

ISNUMERIC

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

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

jlennartz SSCommitted Points: 1574 More actions · Answer 1

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: 281334 More actions · Answer 2

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: 12388 More actions · Answer 3

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 Hall of Fame Points: 3147 More actions · Answer 4

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: 64790 More actions · Answer 5

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 More actions · Answer 6

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

Thanks to all,

Jerry

sknox SSChampion Points: 12388 More actions · Answer 7

jlennartz (9/27/2010)
...With Hugo's explaination it all became clear.

It generally does. 😀

Andrew G SSChampion Points: 12809 More actions · Answer 8

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: 64790 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 64790 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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

Thanks!!!