According to SQL Server’s Books Online (BOL), the ISNUMERIC() function an easy and convenient way to determine whether an given expression can be evaluate as one of SQL Server’s valid numeric data types. These are INTEGER, FLOAT (REAL), DECIMAL (NUMERIC) and MONEY.
Now, unfortunately, this function is buggy and can easily be fooled. Consider this:
SELECT
ISNUMERIC(‘€+,.’)
, ISNUMERIC(‘1d2’)
, ISNUMERIC(‘123,45.’)
———– ———– ———–
1 1 1
Again, going back to BOL: “A return value of 1 guarantees that expression can be converted to one of these numeric types.”
When you attempt to convert these values now into one of the aforementioned data types, you find that the first value can be converted into MONEY and will yield .0000, while the second is interpreted as a FLOAT value of 100, and finally the third is again a MONEY value of 12345.
Well, from the source data, one is inclined to say that only the second value, 1d2, makes some sense, while one expects the first to throw an error, and the third, if convertible at all, to yield 123.45. Interestingly, when you modify the third value to:
SELECT
ISNUMERIC(‘123,3,45.’)
you can’t convert.
SELECT
ISNUMERIC(‘123,345,678,90.’)
again returns 1.
You might be tempted now to say: So what? Well, this might be not a big thing to you, but be careful when you load and/or insert data that relies on the ISNUMERIC function. As you have seen, there are many cases where you don’t get from this function what you expect.