7

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?

gfrizzle
  • 12,419
  • 19
  • 78
  • 104

2 Answers2

11

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

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  ¥
Brad
  • 15,361
  • 6
  • 36
  • 57
  • Actually, it should be `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` – D'Arcy Rittich Dec 13 '10 at 15:52
  • Thanks @RedFilter, I just copied/pasted from the article (since the site requires registration to view it). I incorporated your update. – Brad Dec 13 '10 at 15:58