13 January 2011

IsNumeric() Broken? Only up to a point.

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.

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,

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.

but it is harder to predict which data type will accept a ‘+’ sign.

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).

Also the CAST function itself is quirky in that it cannot convert perfectly reasonable string-representations of integers into integers

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.

Which gives the result …

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 ..

.. 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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 7344 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Follow on

View all articles by Phil Factor