IsNumeric() Broken? Only up to a point.
- by Phil Factor
In SQL Server, probably the best-known 'broken' function is poor ISNUMERIC() . The documentation says 'ISNUMERIC returns 1 when the input expression evaluates to a valid
numeric data type; otherwise it returns 0. ISNUMERIC returns 1 for some characters that are not numbers, such as
plus (+), minus (-), and valid currency symbols such as the dollar sign
($).'Although it will take numeric data types (No, I don't understand why either), its main use is supposed to be to test strings to make sure that you can convert them to whatever numeric datatype you are using (int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, or real). It wouldn't actually be of much use anyway, since each datatype has different rules. You actually need a RegEx to do a reasonably safe check. The other snag is that the IsNumeric() function is a bit broken. SELECT ISNUMERIC(',')This cheerfully returns 1, since it believes that a comma is a currency symbol (not a thousands-separator) and you meant to say 0, in this strange currency. However, SELECT ISNUMERIC(N'£')isn't recognized as currency. '+' and '-' is seen to be numeric, which is stretching it a bit. You'll see that what it allows isn't really broken except that it doesn't recognize Unicode currency symbols: It just tells you that one numeric type is likely to accept the string if you do an explicit conversion to it using the string. Both these work fine, so poor IsNumeric has to follow suit.
SELECT CAST('0E0' AS FLOAT)SELECT CAST (',' AS MONEY) but it is harder to predict which data type will accept a '+' sign. SELECT CAST ('+' AS money) --0.00SELECT CAST ('+' AS INT) --0SELECT CAST ('+' AS numeric)/* Msg 8115, Level 16, State 6, Line 4 Arithmetic overflow error converting varchar to data type numeric.*/SELECT CAST ('+' AS FLOAT)/*Msg 8114, Level 16, State 5, Line 5Error converting data type varchar to float.*/>
So we can begin to say that the maybe IsNumeric isn't really broken, but is answering a silly question 'Is there some numeric datatype to which i can convert this string? Almost, but not quite. The bug is that it doesn't understand Unicode currency characters such as the euro or franc which are actually valid when used in the CAST function. (perhaps they're delaying fixing the euro bug just in case it isn't necessary).SELECT ISNUMERIC (N'?23.67') --0SELECT CAST (N'?23.67' AS money) --23.67SELECT ISNUMERIC (N'£100.20') --1SELECT CAST (N'£100.20' AS money) --100.20
Also the CAST function itself is quirky in that it cannot convert perfectly reasonable string-representations of integers into integersSELECT ISNUMERIC('200,000') --1SELECT CAST ('200,000' AS INT) --0/*Msg 245, Level 16, State 1, Line 2Conversion failed when converting the varchar value '200,000' to data type int.*/ A more sensible question is 'Is this an integer or decimal number'. This cuts out a lot of the apparent quirkiness. We do this by the '+E0' trick. If we want to include floats in the check, we'll need to make it a bit more complicated. Here is a small test-rig.
SELECT PossibleNumber, ISNUMERIC(CAST(PossibleNumber AS NVARCHAR(20)) + 'E+00') AS Hack, ISNUMERIC (PossibleNumber + CASE WHEN PossibleNumber LIKE '%E%' THEN '' ELSE 'E+00' END) AS Hackier, ISNUMERIC(PossibleNumber) AS RawIsNumericFROM (SELECT CAST(',' AS NVARCHAR(10)) AS PossibleNumber UNION SELECT '£' UNION SELECT '.' UNION SELECT '56' UNION SELECT '456.67890' UNION SELECT '0E0' UNION SELECT '-' UNION SELECT '-' UNION SELECT '.' UNION SELECT N'?' UNION SELECT N'¢' UNION SELECT N'?' UNION SELECT N'?34.56' UNION SELECT '-345' UNION SELECT '3.332228E+09') AS examples
Which gives the result ...
PossibleNumber Hack Hackier RawIsNumeric-------------- ----------- ----------- ------------? 0 0 0- 0 0 1, 0 0 1. 0 0 1¢ 0 0 1£ 0 0 1? 0 0 0?34.56 0 0 00E0 0 1 13.332228E+09 0 1 1-345 1 1 1456.67890 1 1 156 1 1 1
I suspect that this is as far as you'll get before you abandon IsNumeric in favour of a regex.
You can only get part of the way with the LIKE wildcards, because you cannot specify quantifiers. You'll need full-blown Regex strings like these
..[-+]?\b[0-9]+(\.[0-9]+)?\b #INT or REAL[-+]?\b[0-9]{1,3}\b #TINYINT[-+]?\b[0-9]{1,5}\b #SMALLINT.. but you'll get even these to fail to catch numbers out of range.So is IsNumeric() an out and out rogue function? Not really, I'd say, but then it would need a damned good lawyer.
