Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ISNUMERIC() and REPLICATE() Expand / Collapse
Author
Message
Posted Monday, November 23, 2009 9:42 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:55 AM
Points: 877, Visits: 876
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
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: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
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
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: 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.
Post #823721
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: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
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
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: 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 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

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
Posted Tuesday, November 24, 2009 6:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:55 AM
Points: 877, Visits: 876
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
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: 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)
Post #823823
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: Monday, September 22, 2014 10:49 AM
Points: 782, Visits: 731
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

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: 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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse