Isnumeric Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Isnumeric Function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-25 : 08:55:54

Steve writes "I am testing the following T-SQL code and found the following strange behavior I have yet to explain....

declare @xxx varchar(7)

select @xxx = '00F3390'

if isnumeric(@xxx) = 0
begin
print 'not numeric'
end
else
begin
print 'yes numeric'
end

The result is that it prints out 'not numeric'. Which is the expeted result. However when you substitute the letter 'D' or 'E' it prints out 'yes numeric'.... For other letters, it seems to work fine.

I've check the configured language , compatibility level, collation, hex values, etc. - Seems strange to me. Maybe you can help."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-25 : 09:53:13

It's because it evaluates to a valid float datatype.

I can see why E works (exponential) don't know about d though (decimal?).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-02-25 : 10:40:08

Whatever value you are trying to check to see if it is numeric, first check and see if you can convert it to a float. If you can, then an isnumeric check will return true.

*************************
Just trying to get things done

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-25 : 12:09:17

out of interest have just tested it...same happens in VB6....not at all surprising...m$ involved in both!

I suppose one man's interpretation of a number is different from anothers...most of us probably assume that isnumeric meant "does the field contain numbers 0-9 + appropriate formatting characters..."

and not "the above plus anything else that can be expressed in scientific notation!"

how doesn't one test if the number can be converted to a float?
or simpler (??)...if it's "numeric other than in scientific format"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-25 : 12:42:46

D is treated as a synonym for E in char to float conversions.
SELECT ISNUMERIC('¤+,.')

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-25 : 19:04:42

Thanks - suspected it was something like that.

>> most of us probably assume that isnumeric meant "does the field contain numbers 0-9 + appropriate formatting characters.."

Nope it's anything that can be converted to the specified datatypes as given in the documentation - usually float covers them all.
A lot of people try to use functions and assume that they were built for the specific case they are concerned with - and get surprised. Always worth testing things even if they seem clear.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-26 : 04:44:25

For tasks like data cleansing, the functionality of ISNUMERIC to only tell you that the value can be cast to one of four numeric types, without telling you which is really unhelpful.
However, even on those terms it doesn't work for Unicode strings. If you create a temporary table with each single character string that ISNUMERIC returns 1 for, you'll see that there are some pretty odd characters in there:


CREATE TABLE #ch (i int, c nchar(1))
DECLARE @i int

SET @i = 32
WHILE @i < 65536
BEGIN
  IF @i = 128 SET @i = 160
  IF ISNUMERIC(NCHAR(@i)) = 1 INSERT INTO #ch VALUES (@i, NCHAR(@i))
  SET @i = @i + 1
END

SELECT * FROM #ch
DROP TABLE #ch

Not sure if this will look right, but here are the characters I get (SQL Server 2000 sp2):
$+,-.0123456789 £¤¥‐‑⁴⁵⁶⁷⁸₀₁₂₃₄₅₆₇₈₉₤€−∞─┌┐└┘├┬┴┼═╒╓╔╕╖╗╘╙╚╛╜╝╤╥╦╧╨╩╪╫╬☼＄＋，－．０１２３４５６７８９
So quite a lot of currency characters, some punctuation, superscript (except 0, 1, 2, 3 and 9!) and subscript digits, infinitiy, linedraw characters, 'white sun with rays', and the full-width versions of the ASCII matches.
Try one of those linedraw chars:


SELECT ISNUMERIC(NCHAR(9496))
GO
SELECT CAST(NCHAR(9496) AS money)
GO
SELECT CAST(NCHAR(9496) AS float)
GO
SELECT CAST(NCHAR(9496) AS int)
GO
SELECT CAST(NCHAR(9496) AS decimal)
GO

It doesn't convert to any numeric type.

As an aside, the infinity character is numeric:


SELECT ISNUMERIC(NCHAR(8734))
GO
SELECT CAST(NCHAR(8734) AS money)
GO
SELECT CAST(NCHAR(8734) AS float)
GO
SELECT CAST(NCHAR(8734) AS int)
GO
SELECT CAST(NCHAR(8734) AS decimal)
GO

it can be cast to float... with a value of 8.0.

Edited by - Arnold Fribble on 02/26/2002 04:48:10

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-26 : 10:03:23

So infinity = 8.0.
That saves learning the other numbers.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources