Why IsNumeric() is no good

Category : Articles User Rating : 5 Stars      Views : 2.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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.
Link back to this article : https://www.sqlmatters.com/Articles/Why IsNumeric is no good.aspx

Keywords

SQL,scripts,TSQL


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered