SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ron Moses / ISNUMERIC() and REPLICATE() / Latest Posts

RE: ISNUMERIC() and REPLICATE()

Steve Jones - SSC Editor — Tue, 24 Nov 2009 08:03:49 GMT

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.

RE: ISNUMERIC() and REPLICATE()

crashdan — Tue, 24 Nov 2009 07:17:32 GMT

[quote][b]crussell-931424 (11/24/2009)[/b][hr]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 0SELECT ISNUMERIC(REPLICATE('0', 7691) + REPLICATE('1', 310)) returns 1The 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.[/quote]i agree 0= 000 but 1 <> 111

RE: ISNUMERIC() and REPLICATE()

crussell-931424 — Tue, 24 Nov 2009 07:16:17 GMT

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 0SELECT ISNUMERIC(REPLICATE('0', 7691) + REPLICATE('1', 310)) returns 1The 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.

RE: ISNUMERIC() and REPLICATE()

Dude76 — Tue, 24 Nov 2009 07:16:00 GMT

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 :D

RE: ISNUMERIC() and REPLICATE()

crashdan — Tue, 24 Nov 2009 07:08:41 GMT

[quote][b]dun (11/24/2009)[/b][hr]That's a big number. How do you report that on your financials? Numbers represented in $000,000,...,000,000? :-PIt made me do a search on the net for big number names (see [url]http://www.sizes.com/numbers/big_numName.htm[/url]). It was interesting to find that the number 1 billion can mean something different in other countries. Fantastic.[/quote]You have now ruined my morning ... HAHA ... i am doing the same.

RE: ISNUMERIC() and REPLICATE()

dun — Tue, 24 Nov 2009 06:51:21 GMT

That's a big number. How do you report that on your financials? Numbers represented in $000,000,...,000,000? :-PIt made me do a search on the net for big number names (see [url]http://www.sizes.com/numbers/big_numName.htm[/url]). It was interesting to find that the number 1 billion can mean something different in other countries. Fantastic.

RE: ISNUMERIC() and REPLICATE()

crashdan — Tue, 24 Nov 2009 06:22:49 GMT

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. :-D

RE: ISNUMERIC() and REPLICATE()

Dude76 — Tue, 24 Nov 2009 06:19:33 GMT

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 ! :)

RE: ISNUMERIC() and REPLICATE()

ronmoses — Tue, 24 Nov 2009 06:11:06 GMT

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.

RE: ISNUMERIC() and REPLICATE()

Ray Festino — Tue, 24 Nov 2009 06:05:35 GMT

The answer is not completely accurate stating that 'any string longer than 309' will return zero.SELECT ISNUMERIC(REPLICATE('0', 610)) returns '1'.Ray

RE: ISNUMERIC() and REPLICATE()

vk-kirov — Tue, 24 Nov 2009 03:00:47 GMT

[quote][b]ZeroFusion (11/24/2009)[/b]As for the length constraint - according to the [url=http://msdn.microsoft.com/en-us/library/aa258271%28SQL.80%29.aspx]MS SQL Data Types[/url] 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.[/quote]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".[quote]ISNUMERIC() returns a 0 against [b]any[/b] string longer than 309 characters, even if it is numeric.[/quote]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:[code="sql"]SELECT ISNUMERIC('-' + REPLICATE('1', 309)), ISNUMERIC('+' + REPLICATE('1', 309) + '.'), ISNUMERIC(REPLICATE('1', 300) + '.' + REPLICATE('1', 299))[/code]These strings can be converted to the [b]float[/b] data type. So ISNUMERIC() returns 1 as a result.Please correct the explanation... [b]ZeroFusion[/b] 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 :-)

RE: ISNUMERIC() and REPLICATE()

Saurabh Dwivedy — Tue, 24 Nov 2009 01:42:56 GMT

Zerofusion - thanks for clearing my doubt.

RE: ISNUMERIC() and REPLICATE()

ZeroFusion — Tue, 24 Nov 2009 01:29:37 GMT

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 [url=http://msdn.microsoft.com/en-us/library/ms186272.aspx]MSDN article[/url] on IsNumeric().As for the length constraint - according to the [url=http://msdn.microsoft.com/en-us/library/aa258271%28SQL.80%29.aspx]MS SQL Data Types[/url] 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:[code="sql"]-- Returns 0, 1 respectivelySELECT IsNumeric('2' + REPLICATE('0', 308)), IsNumeric('179' + REPLICATE('0', 306));[/code]So numbers between 1.79E + 308 and 2.23E + 308 seem not to be taken into account.

RE: ISNUMERIC() and REPLICATE()

Saurabh Dwivedy — Tue, 24 Nov 2009 00:57:33 GMT

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?

ISNUMERIC() and REPLICATE()

ronmoses — Mon, 23 Nov 2009 21:42:51 GMT

Comments posted to this topic are about the item [B]ISNUMERIC() and REPLICATE()[/B]