CONVERT() to float using scientific notation

Someone posted to #sqlhelp on Twitter, asking the following: “Wondered if anyone could enlighten me as to why ISNUMERIC(‘7d8’) returns 1?”

Sure enough, SELECT ISNUMERIC('7d8') returns a 1.

WTF? d isn’t numeric!

OK, sure, if you say “seven-dee-eight” out loud it sounds like a number, but “d” is a letter, not a number.

I’m sure everyone’s first reaction is to try converting to int or bigint:

SELECT CAST('7d8' AS bigint);

Nope, that returns an error:

Msg 8114, Level 16, State 5, Line 13
Error converting data type varchar to bigint.

Obviously. Because “d” isn’t an integer. But if you cast to float, it works just fine:

SELECT CAST('7d8' AS float);

You’re probably guessing “Oh, that’s totally a hex value. I guess that makes sense!” Except that that 7d8 is hexadecimal for 2008. Casting to float returns 700,000,000.

It’s a “feature”

If the title of this post didn’t give it away, SQL Server considers scientific notation to be a valid way to represent a floating point number. 7d8 gets treated as “7 decimal polynomial 8” or 7*(10^8). We’re generally more used to seeing this written out in “E notation” (perhaps on a calculator …. do kids still use calculators? There’s an app for that). That “E” means “exponent” which is easier to understand if you aren’t a math geek.

Truth is, SQL Server lets you use either a D or an E in scientific notation, thus making for multiple ways to confuse you. Does the E notation of 7ᴱ8 look more familiar?

What if it was written as 7.890123456ᴱ8? That’s usually the format that popped up on your trusty Texas Instruments calculator. Guess, what? SQL Server knows how to convert that to a float:

SELECT CAST('7.890123456e8' AS float);
--result:
--   789012345.6

And if you cast a really big or really small number to float, it will return in scientific notation. You’ll notice that SQL Server explicitly includes a + or – on the right-hand exponent, which determines if it’s a big or small number:

SELECT CAST(123456789012345678901234567890 AS float);
--result:
--   1.23456789012346E+29
SELECT CAST(0.00000000000000000000123456789 AS float);
--result:
--   1.23456789E-21

Getting back to ISNUMERIC()

The original question asked about why ISNUMERIC() considered 7d8 to be a number. BOL explains that “ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include…” <a list of datatypes>.

That list of datatypes includes both the float and money datatypes, in addition to the usual suspects. The function really is looking to see if the input value can be converted to ANY valid number. I think we often think of “number” as meaning “a base-10 number represented only with numeric characters and maybe a decimal point.” Well, if that’s what you want, ISNUMERIC() isn’t the answer.

If you want to test if a string is a specific type of number, then you probably want to use TRY_CONVERT(). BOL will tell you the details. The function was introduced in 2012, so unless you’re stuck on an older version, you can use it to check if a value converts to a specific datatype.

Is there a moral to this story?

Maybe. Actually, there are a few:

1. Dirty data is a pain in your butt. 
2. Validate inputs at the application. If your application made sure the input was the right type of numeric, and didn’t pass it as a string, your life would be easier.
3. Don’t assume. Even something as straightforward as “is numeric” might not be straightforward.
4. Be specific. If you want to check “is this varchar value a valid int?”, then be very specific in checking for that scenario. “is this varchar value numeric?” is not actually the same question. Numeric is a class of data types, not a specific data type.
5. RTFM. I forget this all the time. Time and time again, I find the answer is somewhere in Books Online. The answer is there–I just didn’t read it.