# Problem with ISNUMERIC

Discussion in 'General Developer Questions' started by vietcave, Sep 8, 2005.

1. ### vietcaveNew Member

Hi everyone,

I find out the problem with ISNUMBERIC.

SELECT ISNUMERIC('1010') ---> 1: exactly

SELECT ISNUMERIC('10C0') ---> 0: exactly

SELECT ISNUMERIC('10E0') ---> 1: wrong

Why? Is there something wrong when E appear?

Thanks

2. ### satyaModerator

Yes
Better to use Like '%[0-9]%' than IsNumeric

Failing to plan is Planning to fail
4. ### FrankKalisModerator

Don't rely on ISNUMERIC!

The d(D) and e(E) are used in Fortran to denote a float double precision (d), respectively exponential writing (e). SQL Server internally uses the standard C++ function ATOF() to parse such strings. When you look at the definition of ATOF() it seems to follow the Fortran rules.
'10E0' is interpreted as FLOAT and will yield 10.0. It's a shortcut to 10 * 10^0.
If you need to be sure about your input either use a client language (which are far better suited for such stuff) or use PATINDEX().

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

and also Select IsNumeric('1,100') will return 1
So using Like is the best approach to check for numerics

Failing to plan is Planning to fail
6. ### vietcaveNew Member

Thank all of you very much.
7. ### qd59New Member

Here is my Solution: I made a stored procedure to deal with the numeric problem, you can try.
It will return 0 for non-numeric and 1 for numeric.

--********************************************************
ALTER PROCEDURE usp_CheckNumeric (@DATATOCHECK NVARCHAR (1000))
AS
DECLARE @CHEKRE INT
DECLARE @TTS NVARCHAR (1000)
DECLARE @POS INT
DECLARE @POSCHAR NVARCHAR (1)
--SET @POS = 1
SET @TTS = @DATATOCHECK
SET @CHEKRE = 1
IF LEN (@TTS)=0 OR @TTS IS NULL
BEGIN
SET @CHEKRE = 0
END
ELSE
BEGIN
IF CHARINDEX ('-',@TTS) = 1
BEGIN
SET @POS =2
END
ELSE
BEGIN
SET @POS =1
END

--PRINT @POS
WHILE @POS <= LEN (@TTS) AND @CHEKRE<>0
BEGIN

SELECT @POSCHAR = RIGHT(LEFT(@TTS, @POS),1)
--PRINT @POSCHAR
--PRINT ASCII(@POSCHAR)
IF @POSCHAR='.'
BEGIN
IF CHARINDEX ('.',@TTS) = 1 OR CHARINDEX ('.',@TTS) = LEN(@TTS)
BEGIN
SET @CHEKRE = 0
--PRINT 'ERROR HERE'
END
END
ELSE
BEGIN
SET @POSCHAR = REPLACE(@POSCHAR,'.',0)
IF ASCII(@POSCHAR) NOT IN (48,49,50,51,52,53,54,55,56,57) OR @POSCHAR=' '
BEGIN
SET @CHEKRE = 0
END
ELSE
BEGIN
SET @CHEKRE = 1
END
END

SET @POS = @POS + 1
END
--END
END
RETURN @CHEKRE
GO
8. ### FrankKalisModerator

Looks a bit like an overkill to me, but if it's working...[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />