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
http://www.aspfaq.com/show.asp?id=2390 for your joy. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
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 Madhivanan Failing to plan is Planning to fail
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
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 />
But It seems like using Cursors<br /><br />Where col not like '%[a-zA-Z]%' -- and other characters <br />will work I think [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
here is another instance where isnumeric function fails. select isnumeric('+') select isnumeric('-') both cases returns 1 better to go with wildcard approach