Scientific Notation with IsNumeric()

Be careful when using the built-in IsNumeric() function. When presented with a character string formatted as “number+D+number” or “number+E+number”, the function will return a value of 1 (true). This is because SQL Server evaluates [number][d,e][number] as scientific notation.

For example,

[cc lang=”tsql”]
Declare @dNotation char(10)
, @eNotation char(10)
, @standardText char(5);

Select @dNotation = ‘1d1’
, @eNotation = ‘1e1’
, @standardText = ‘abcde’;

Select IsNumeric(@dNotation) As ‘dNotation’
, IsNumeric(@eNotation) As ‘eNotation’
, IsNumeric(@standardText) As ‘standardText’;
[/cc]

… will return:

dNotation   eNotation   standardText
————————————————-
1           1            0

I’ve seen a couple of different ways to approach this, and each have their merits. The most easy and elegant solution I’ve seen came from Dave C. — PatIndex(‘%[^0123456789]%’, columnName):

[cc lang=”tsql”]
Select PatIndex(‘%[^0123456789]%’, @dNotation)
, PatIndex(‘%[^0123456789]%’, @eNotation)
, PatIndex(‘%[^0123456789]%’, @standardText);
[/cc]

I’ve also seen Replace() and user-defined functions used with great effect.

Thanks to Dave C. for sharing his solution! 🙂

HTH!

Michelle