Why is "0D0" considered numeric in SQL Server 2008?

Any idea why ISNUMERIC('0D0') = 1 is TRUE in SQL Server 2008?

I'm validating ID numbers from another system, which sometimes contain letters that we don't want to bring over, but this combination is tripping up the code (the specific ID that it thinks is numeric is "005406257D6"). Downstream we're doing CONVERT(BIGINT, @val), which is obviously choking when it finds these "D" values.

What special case am I hitting开发者_运维百科, and how do I account for it?

Basically, IsNumeric is useless - all it verifies is that the string can be converted to any of the numeric types. In this case, "0D0" can be converted to a float. (I can't remember the reason, but effectively "D" is a synonym for "E", and means it's scientific notation):

select CONVERT(float,'2D3')

2000

If you want to verify that it's just digits, then not @val like '%[^0-9]%' would be a better test. You can improve this further by adding a length check also.

An good explanation of why.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

(below is @RedFilter's more exhaustive query/list)

SELECT  [Ascii Code] = STR(Number)
       ,[Ascii Character] = CHAR(Number)
       ,[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
FROM    Master.dbo.spt_Values
WHERE   Type = 'P'
        AND Number BETWEEN 0 AND 255
        AND ISNUMERIC(CHAR(Number)) = 1
UNION
SELECT  [Ascii Code] = STR(Number)
       ,[Ascii Character] = CHAR(Number)
       ,[ISNUMERIC Returns] = ISNUMERIC('0' + CHAR(Number) + '0')
FROM    Master.dbo.spt_Values
WHERE   Type = 'P'
        AND Number BETWEEN 0 AND 255
        AND ISNUMERIC('0' + CHAR(Number) + '0') = 1

yeilds

Ascii Code Ascii Character
---------- ---------------
     0      
     9      
    10      
    11      
    12      
    13      
    36  $   
    43  +   
    44  ,   
    45  -   
    46  .   
    48  0   
    49  1   
    50  2   
    51  3   
    52  4   
    53  5   
    54  6   
    55  7   
    56  8   
    57  9   
    68  D   
    69  E   
    92  \   
   100  d   
   101  e   
   128  €   
   160      
   162  ¢   
   163  £   
   164  ¤   
   165  ¥