Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ron Moses » ISNUMERIC() and REPLICATE()
Add to briefcase
15 posts, Page 1 of 2
12»»

ISNUMERIC() and REPLICATE() Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ronmoses
Posted Monday, November 23, 2009 9:42 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 651, Visits: 688
Comments posted to this topic are about the item ISNUMERIC() and REPLICATE()

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #823624
Saurabh Dwivedy
Posted Tuesday, November 24, 2009 12:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, January 05, 2012 2:05 AM
Points: 488, Visits: 335
Why so, though? First, why does ISNUMERIC return 1 when obviously parameter is of type char and not numeric? And then even assuming that a string of 1's can be treated as numeric because it has nothing but numbers in it why the limit to 309? Can anyone throw some light?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
Post #823715
ZeroFusion
Posted Tuesday, November 24, 2009 1:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:36 AM
Points: 300, Visits: 151
Sauirabh, IsNumeric() returns 1 if the parameter could be converted to a number - but it doesn't have to be a numeric. So IsNumerc('123') will return 1, because you can easily convert string '123' to a number 123. More information can be found in MSDN article on IsNumeric().

As for the length constraint - according to the MS SQL Data Types list, "Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308". Such numbers have 309 digits - hence the limitation.

However, either I don't understand something or there's something wrong, because my MS SQL 2005 installation returns the following:
-- Returns 0, 1 respectively
SELECT IsNumeric('2' + REPLICATE('0', 308)), IsNumeric('179' + REPLICATE('0', 306));

So numbers between 1.79E + 308 and 2.23E + 308 seem not to be taken into account.
Post #823721
Saurabh Dwivedy
Posted Tuesday, November 24, 2009 1:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, January 05, 2012 2:05 AM
Points: 488, Visits: 335
Zerofusion - thanks for clearing my doubt.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
Post #823725
vk-kirov
Posted Tuesday, November 24, 2009 3:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 3,192, Visits: 4,151
ZeroFusion (11/24/2009)
As for the length constraint - according to the MS SQL Data Types list, "Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308". Such numbers have 309 digits - hence the limitation.
...
So numbers between 1.79E + 308 and 2.23E + 308 seem not to be taken into account.

There's a typo in BOL2000. The correct range is "-1.79E + 308 through -2.23E - 308, 0 and 2.23E - 308 through 1.79E + 308".

ISNUMERIC() returns a 0 against any string longer than 309 characters, even if it is numeric.

Is this considered to be an explanation? This is completely incorrect. There are many 310-, 311-, and even 600-character long strings for which ISNUMERIC returns a nonzero value, for example:
SELECT
    ISNUMERIC('-' + REPLICATE('1', 309)),
    ISNUMERIC('+' + REPLICATE('1', 309) + '.'),
    ISNUMERIC(REPLICATE('1', 300) + '.' + REPLICATE('1', 299))

These strings can be converted to the float data type. So ISNUMERIC() returns 1 as a result.

Please correct the explanation... ZeroFusion said about data type conversions - that's the reason.

Anyway, thanks to the author for making me to do some investigation about SQL Server data types and data type conversions. It was quite interesting
Post #823736
Ray Festino
Posted Tuesday, November 24, 2009 6:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 24, 2009 7:56 AM
Points: 219, Visits: 38
The answer is not completely accurate stating that 'any string longer than 309' will return zero.

SELECT ISNUMERIC(REPLICATE('0', 610)) returns '1'.

Ray

Kindest Regards,

Ray Festino
Post #823812
ronmoses
Posted Tuesday, November 24, 2009 6:11 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 651, Visits: 688
I would be happy to correct the explanation but I can find no means of doing so. I assume this ability is restricted to administrators, or else it is very difficult to find.

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #823817
Dude76
Posted Tuesday, November 24, 2009 6:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:52 PM
Points: 280, Visits: 88
I was a little lucky on that question because i didn't remember the limit of a float was 1,79E+308 (so 309 length), i thought of a very shortener length !
309/310, close question about float limit string representation ! :)

My MCP Transcript (ID : 692471 Access : 109741229)
Post #823823
crashdan
Posted Tuesday, November 24, 2009 6:22 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: 2 days ago @ 9:24 AM
Points: 770, Visits: 682
I can only assume the issue with replicating 610 zeros is the number property of zero. But regardless that is a good catch ... made me think a bit.

Post #823824
dun
Posted Tuesday, November 24, 2009 6:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 07, 2012 8:25 AM
Points: 540, Visits: 245
That's a big number. How do you report that on your financials? Numbers represented in $000,000,...,000,000?

It made me do a search on the net for big number names (see http://www.sizes.com/numbers/big_numName.htm). It was interesting to find that the number 1 billion can mean something different in other countries. Fantastic.
Post #823842
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.