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.
not at the start
NOT LIKE on that expression since it has the ^ at the beginning indicating NOT. I.e. you are asking "Is it NOT not all numbers?"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 ¥
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