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

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Bookmark the permalink.

3 Responses to Scientific Notation with IsNumeric()

  1. Yuriy says:

    Not only notation letters can throw IsNumeric off, but other characters as well. I’ve used UDF described at http://codecorner.galanter.net/?p=146 and it seems to work pretty good. Thanks for the alternative though :)

  2. Bruce W Cassidy says:

    Try:

    PatIndex(‘%[^0-9]%’, columnName)

  3. Louie Bao says:

    IsNumeric returns true not only for scientific notations, but also monetary symbols as well, please refer to my blog on this topic http://www.louiebao.net/blog/200910/isnumeric/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>