What is wrong with IsNumeric()?
Posted by Viral Sarvaiya on May 18, 2012
Today I face very strange thing with IsNumeric() function in sql
select IsNumeric('1234D234') select IsNumeric('123A123')
Both query have alphanumeric value and both give different answer. very strange.
After a long time spent on google i get solution. We can say or cannot say that there are bug in IsNumeric() function.
IsNumeric() can falsely interpret non-numeric letters and symbols (such as D, E, and £, $), and even tabs (CHAR(9)) as numeric.
With this character, Why IsNumeric() can not give right answer? because D stand for Decimal. that value consider as a decimal value, E stand for exponential and $,£ is stand for currency symbol. so Isnumeric() fail to convert those values which has like D,E,$,£ characters.
so to get around this problem, we can create user define function which find the value is integer/numeric. as below.
CREATE FUNCTION dbo.CheckNumeric ( @num VARCHAR(64) ) RETURNS BIT BEGIN IF LEFT(@num, 1) = '-' SET @num = SUBSTRING(@num, 2, LEN(@num)) DECLARE @pos TINYINT SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num)) RETURN CASE WHEN PATINDEX('%[^0-9.-]%', @num) = 0 AND @num NOT IN ('.', '-', '+', '^') AND LEN(@num)>0 AND @num NOT LIKE '%-%' AND ( ((@pos = LEN(@num)+1) OR @pos = CHARINDEX('.', @num)) ) THEN 1 ELSE 0 END END GO
CREATE FUNCTION dbo.CheckInteger ( @num VARCHAR(64) ) RETURNS BIT BEGIN IF LEFT(@num, 1) = '-' SET @num = SUBSTRING(@num, 2, LEN(@num)) RETURN CASE WHEN PATINDEX('%[^0-9-]%', @num) = 0 AND CHARINDEX('-', @num) <= 1 AND @num NOT IN ('.', '-', '+', '^') AND LEN(@num)>0 AND @num NOT LIKE '%-%' THEN 1 ELSE 0 END END GO
Learn From: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html