Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

ISNUMERIC Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 10:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:53 PM
Points: 790, Visits: 1,192
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
Post #993806
Posted Monday, September 27, 2010 11:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 18,078, Visits: 16,112
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
Post #993821
Posted Monday, September 27, 2010 11:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:23 PM
Points: 1,391, Visits: 1,758
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.
Post #993835
Posted Monday, September 27, 2010 11:24 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:20 AM
Points: 1,490, Visits: 1,060
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.
Post #993836
Posted Monday, September 27, 2010 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 6,153, Visits: 8,412
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 &lt; 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
Post #993842
Posted Monday, September 27, 2010 11:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:53 PM
Points: 790, Visits: 1,192
Thank You sknox, wware and especially Hugo. With Hugo's explaination it all became clear.

Thanks to all,
Jerry
Post #993867
Posted Monday, September 27, 2010 11:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:23 PM
Points: 1,391, Visits: 1,758
jlennartz (9/27/2010)
...With Hugo's explaination it all became clear.

It generally does.
Post #993874
Posted Monday, September 27, 2010 6:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:42 PM
Points: 1,310, Visits: 1,786
Why does
select i+1, char(i+1), isnumeric(char(i+1)) from l where i &lt; 127

need an i+1 in each column?
Post #994132
Posted Tuesday, September 28, 2010 12:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 6,153, Visits: 8,412
foxxo (9/27/2010)
Why does
select i+1, char(i+1), isnumeric(char(i+1)) from l where i &lt; 127

need an i+1 in each column?

If you use
select i+1, char(i), isnumeric(char(i)) from l where i &lt; 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
Post #994203
Posted Wednesday, September 29, 2010 1:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, 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
Post #994960
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse