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
Or
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
Thanks.
Kelly's Chronicles said
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Jeff Moden said
Actually, there is no bug in ISNUMERIC. **ISNUMERIC does exactly what it was designed to do.** It does NOT “fail” in the examples you gave. It returns a “1″ if the data can be converted to ANY numeric datatype. The includes things that have “white-space” like tabs in them. It includes certain letters like “E” and “D” for different forms of scientific notation. It includes certain punctuation like commas and decimal points. It includes signs like “+”, ‘-’, currency symbols, and parenthesis for certain forms of negative numbers. Etc, etc.
The REAL problem with ISNUMERIC is what people “think” ISNUMERIC should mean. It certainly DOESN’T mean “IsAllDigits” or “IsADecimalNumber” or “IsAnInteger”. It simply means that the character can be converted to some form of numeric datatype even if it’s not the datatype you’re trying to check for.
Please see the following article for more information.
http://www.sqlservercentral.com/articles/IsNumeric/71512/
Viral Sarvaiya said
hay Jeff,
thanks for improving knowledge…