SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNUMERIC


ISNUMERIC

Author
Message
jlennartz
jlennartz
SSC Eights!
SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)

Group: General Forum Members
Points: 938 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
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67091 Visits: 18570
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

sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 2928
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
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 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 l

That should help you begin to understand what's going on.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
jlennartz
jlennartz
SSC Eights!
SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)

Group: General Forum Members
Points: 938 Visits: 1197
Thank You sknox, wware and especially Hugo. With Hugo's explaination it all became clear.

Thanks to all,
Jerry
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 2928
jlennartz (9/27/2010)
...With Hugo's explaination it all became clear.

It generally does. :-D
Andrew G
Andrew G
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4077 Visits: 2282
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search