We all know what
IsNumeric() does, don’t we ? According to the Books Online
definition “ISNUMERIC returns 1 when the
input expression evaluates to a valid numeric data type; otherwise it returns 0.”
So
SELECT ISNUMERIC('123')
SELECT ISNUMERIC('abc')
return 1 and 0 respectively, as we would expect.
However what about the following ? :
SELECT ISNUMERIC('1e2')
SELECT ISNUMERIC('+')
SELECT ISNUMERIC('.')
SELECT ISNUMERIC('-')
SELECT ISNUMERIC('10D2')
SELECT ISNUMERIC('$2')
SELECT ISNUMERIC(',12,,3')
None of these look like numerics so they should all return 0, right ?
Wrong ! They all returns 1.
What's going on here ? The problem is that ISNUMERIC is too simplisitic. The characters in the strings above are
sometimes valid in numbers, for
instance +123 and 12,000 are strings that can be converted to numbers, where as 12+3 and 1,20,00 can't be converted because they
don't make sense as numbers. ISNUMERIC seems to check what
ASCII characters are present rather than interpreting the context of the whole string, so it is really
saying that the string
might be a valid number. That’s not much use for ensuring that a string can
definitely be converted to a string.
So what to do ? Many people use CLR functions for validation. Alternatively if you only want to catch
strings that only contain digits you can use a predicate like
NOT LIKE '%[^0-9]%'
However this is only suitable for small amounts of data as it won’t scale very well for larger data sets.