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

http://codesimplified.com/2012/05/18/what-is-wrong-with-isnumeric/

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

Thanks.