IS NUMERIC OUTPUT

Question

IS NUMERIC OUTPUT

Prakash Sawant

SSCrazy

Points: 2173
More actions
April 20, 2007 at 10:21 am

#173728

Below is the code if i run this on SQL 2000 & 2005 i get different output, can any one tell me why is it so?

IF ISNUMERIC(SUBSTRING('VDHL1PY\117',8,2))= 1
BEGIN
PRINT 'IS NUMERIC'
END
ELSE
PRINT 'NOT NUMERIC'
------------
Prakash Sawant
http://psawant.blogspot.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron Rice Valued Member Points: 60 More actions · Answer 1

There doesn't seem to be much rhyme nor reason to the isnumeric() function. I don't have SQL Server 2000 nearby to test it, but in 2005 I ran this script and saw some very strange "numeric" characters indeed:

declare

@x int; set @x = 0

while

@x < 256

begin

set @x = @x + 1; if isnumeric(char(@x)) = 1 print char(@x)

end

It has been suggested elsewhere that there be a variable for the isnumeric() function to indicate a specific data type. So isnumeric('$', 'int') would return 0 while isnumeric('$', 'money') would return 1. One can dream... !

Ron Rice

David McFarland SSChampion Points: 11815 More actions · Answer 2

It seems that "\" has some strange (and undocumented) characteristic in 2005. It seems to turn it into a float or numeric.

Try:

SELECT 1

and

SELECT \1

in a query window, and you'll see what I mean. It doesn't do the same in 2000.

That answers the question as to why the two versions return different results for your query, but it doesn't answer why that behavior is there in the first place.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

IsNumeric is very different that IsAllNumbers. Here's the best way I know how to come to that conclusion (IsAllNumbers)

SELECT CASE WHEN '1/1' NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsAllNumbers

SELECT CASE WHEN '11' NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsAllNumbers

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 4

I had a function in 2000 which use to substring & convert values from varchar column, now this is failing in 2005 because its excepts the "\" char as number but when i assign it int it fails.

I think i need to rewrite the logic considering this issue.

------------
Prakash Sawant

http://psawant.blogspot.com

David McFarland SSChampion Points: 11815 More actions · Answer 5

Prakash, Isnumeric (the T-SQL version) has always been wonky, and many of us have written customized routines to deal with our specific issues because of that. You'll likely find a ton of them on the web due to this, so you don't necessarily have to start from scratch.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 6

David i agree with you on this, even i have customized my function do deal with this issue.

Thank you all very much for your suggestions.

------------
Prakash Sawant

http://psawant.blogspot.com