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

Question

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?

Answer 1

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.