Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

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.

Advertisements

4 Responses to “What is wrong with IsNumeric()?”

  1. http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

    🙂

  2. 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/

  3. Viral Sarvaiya said

    hay Jeff,
    thanks for improving knowledge…

  4. kraaitje said

    This is a fast and easy way to check for numeric value, for example in a join;

    (CASE WHEN IsNumeric(@String+ ‘.0E0’) = 1 THEN CAST(@String AS INT) ELSE NULL END)

    Greets, Ronald

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: