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
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 🙂
Try:
PatIndex(‘%[^0-9]%’, columnName)
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/