Monday, September 17, 2012

Why ISNUMERIC Returns More Than Just Strictly Numeric Strings and What to do Instead

This article (Why doesn’t ISNUMERIC work correctly?) explains why the the SQL ISNUMERIC function returns true for strings with more than just digits and decimal points.  The short answer is that it considers formatted numbers, such as those with currency symbols or commas among others as numeric.

So how do you test for just digits and decimal points?  Use the following comparison:

NOT LIKE '%[^0-9.]%'

This is using a kinda sorta regular expression with the LIKE operator.  '%[^0-9.]%' expression matches the pattern if there is any character that does not match(the ^ character) a digit between 0 and 9 or the decimal point anywhere in the string.  Since this is searching for a pattern that doesn’t match a number you then need to search for values that are NOT LIKE it.

If you want to exclude decimal numbers then just remove the '.' from the character list.

No comments:

Post a Comment