November 23, 2009 at 9:42 pm
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
November 24, 2009 at 12:57 am
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
November 24, 2009 at 1:29 am
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.
November 24, 2009 at 1:42 am
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
November 24, 2009 at 3:00 am
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 🙂
November 24, 2009 at 6:05 am
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
November 24, 2009 at 6:11 am
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
November 24, 2009 at 6:19 am
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 ! 🙂
November 24, 2009 at 6:22 am
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. 😀
November 24, 2009 at 6:51 am
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.
November 24, 2009 at 7:08 am
dun (11/24/2009)
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.
You have now ruined my morning ... HAHA ... i am doing the same.
November 24, 2009 at 7:16 am
Not an every day problem for us (hi from France ^^ ), but as we have to communicate in english for international, it is an usual question 😀
November 24, 2009 at 7:17 am
crussell-931424 (11/24/2009)
Any number of zeros returns 1. So if it is smart enough to know that all the characters are zero and thus must be a number why can't it just check all the characters individually to see if they are 0 to 9? Or is there no such thing as a number larger than a float's maximum value? I just now noticed that it strips away all leading zeros. I guess that answers my own question.SELECT ISNUMERIC(REPLICATE('0', 7690) + REPLICATE('1', 310)) returns 0
SELECT ISNUMERIC(REPLICATE('0', 7691) + REPLICATE('1', 310)) returns 1
The reason then that the second one returns 1 is that there are really only 309 of them. It only considers the first 8000 characters of the string, meaning there are really only 309 of the digit 1, which we already know returns a 1.
i agree 0= 000 but 1 <> 111
November 24, 2009 at 8:03 am
It's a bug in the engine, and I'm not sure how many people will run across it, but it might be nice to know this.
The explanation has been changed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply