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

 ISNUMERIC() and REPLICATE() Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, November 23, 2009 9:42 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, May 25, 2016 11:36 AM Points: 1,045, Visits: 996
 Comments posted to this topic are about the item ISNUMERIC() and REPLICATE() -----a haiku...NULL is not zeroNULL is not an empty stringNULL is the unknown
Post #823624
 Posted Tuesday, November 24, 2009 12:57 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 3, 2016 3:40 AM Points: 488, Visits: 340
 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/dwivedysFor better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537Be Happy!
Post #823715
 Posted Tuesday, November 24, 2009 1:29 AM
 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 respectivelySELECT 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
 Posted Tuesday, November 24, 2009 1:42 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 3, 2016 3:40 AM Points: 488, Visits: 340
 Zerofusion - thanks for clearing my doubt. Saurabh Dwivedy___________________________________________________________My Blog: http://tinyurl.com/dwivedysFor better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537Be Happy!
Post #823725
 Posted Tuesday, November 24, 2009 3:00 AM
 Hall of Fame Group: General Forum Members Last Login: Saturday, June 6, 2015 9:44 PM Points: 3,450, Visits: 4,408
 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
 Posted Tuesday, November 24, 2009 6:05 AM
 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
Post #823812
 Posted Tuesday, November 24, 2009 6:11 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, May 25, 2016 11:36 AM Points: 1,045, Visits: 996
 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 zeroNULL is not an empty stringNULL is the unknown
Post #823817
 Posted Tuesday, November 24, 2009 6:19 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, July 9, 2015 9:04 AM Points: 280, Visits: 94
 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
 Posted Tuesday, November 24, 2009 6:22 AM
 Right there with Babe Group: General Forum Members Last Login: Monday, April 11, 2016 7:58 PM Points: 786, Visits: 757
 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
 Posted Tuesday, November 24, 2009 6:51 AM
 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.
Post #823842

 Permissions