A colleague over the weekend sent a note out to the team requesting any kind of insight.  It turns out that he had a job fail because an instance of the T-SQL ISNUMERIC built-in function behaved in an unexpected way.

According to BoL: “ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.”   Valid numeric data types include the four integer types (tiny, small, “regular” and big), decimal and numeric (which are the same), small money and money, and float and real (which are the same).

The money data types offer opportunities to have a whole host of money designations (such as the $, franc, yen, etc. ) to be embedded in the input string that may result in a false positive with ISNUMERIC.

In our particular example the input value was:

SELECT ISNUMERIC('107D084');

which returned 1.

It turns out that the expression ‘107D084’ is a valid scientific notation;  and is ‘107D-084’ too.

The D stands for DOUBLE FLOAT or the 64-bit version of E, which is SINGLE FLOAT.  The D and E can be either UPPER CASE or lower case.  Either will evaluate to a valid numeric expression.

What makes this even more interesting is that there is no DOUBLE FLOAT data type in SQL Server.  Regardless, the DOUBLE FLOAT is a valid numeric, even if we can not store it to that level of precision.

For the record, there are no other valid letters that can used to represent a scientific notion numeric. 

For a comprehensive dissertation on the isnumeric function check out John Magnabosco’s excellent blog contribution.

So what should we do when we get an unexpected scientific notation value in our data stream?  Chances are what you’re really trying to do is test for a valid integer value.  A quick Google search for ISINTEGER will return several peer reviewed user defined functions.

None of this is rocket science, but avoiding a process failure over the weekend is worth the extra effort to test for what you’re really looking for.

Happy SQL-ng!