

SSC Eights!
Group: General Forum Members
Last Login: Thursday, February 19, 2015 7:19 AM
Points: 942,
Visits: 912


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




SSCAddicted
Group: General Forum Members
Last Login: Sunday, November 2, 2014 9:13 AM
Points: 488,
Visits: 338


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!




Old Hand
Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:01 AM
Points: 300,
Visits: 153


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.




SSCAddicted
Group: General Forum Members
Last Login: Sunday, November 2, 2014 9:13 AM
Points: 488,
Visits: 338





Hall of Fame
Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448,
Visits: 4,407


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 600character 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




SSC 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




SSC Eights!
Group: General Forum Members
Last Login: Thursday, February 19, 2015 7:19 AM
Points: 942,
Visits: 912


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




SSC Veteran
Group: General Forum Members
Last Login: Friday, October 17, 2014 8:46 AM
Points: 280,
Visits: 90


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)




Right there with Babe
Group: General Forum Members
Last Login: Friday, January 23, 2015 11:31 AM
Points: 786,
Visits: 733


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.




Mr or Mrs. 500
Group: General Forum Members
Last Login: Saturday, July 19, 2014 6:50 AM
Points: 540,
Visits: 254


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.



