UMG Developer (9/25/2010)
Thanks for the question.
This should be good to help people understand how ISNUMERIC works...
Yeah... like total crap. Its borderline useless. Just to further demonstrate that.
This gives 1! An 'e' isnt in my book numeric. Now i know SQL thinks its a calculator and thinks '2e1' = 20 (it does the same with a 'd' instead of the 'e'). But seriously, thats stupid.
ISNUMERIC is indeed useless (I even removed the "borderline" here). But not for the reasons you state. The reason it is unseles, is that it tells you if a string can be converted to "any" numerical data type, without bothering to tell you which data type. And since conversion rules are different for different types, a result of ISNUMER(...) = 1 does not guarantee a good conversion.
SQL Server does not think it is a calculator. SQL Server recognises "2e1" as a valid form to input floating point data. The "2e1" is actually shorthand for "2 x 101". In this particular case, writing "20" would have been easier. But do you really prefer "0.0000000000000000000012" over "1.2e-21"? And do you prefer "123000000000000000000000000000000" over "1.23e32"? For those are values well within the data permitted range of floating point numbers.
And another reason for writing "1e2" might be that you need the constant to be regarded as floating point data, to prevent conversions. Just as we sometimes type "3.0" instead of just "3" to be sure that SQL Server treates the constant as numeric and not as int. And just as we type "N'string'" instead of "'string'" to force unicode data.
The "2e1" form is recognised by almost every programming language I have ever used. As is the "2d1" form (the difference is that one is double precision, the other normal precision - I don't know off the top of my head how and even if this translates to different data types in SQL Server, but at least for code compatbility, it recognises both forms).
Like you, I think ISNUMERIC in its current form sucks. I really want Microsoft to give us a function that tells me if I can convert a specific string value in a specific data type. One that would permit a decimal point for conversion to numeric, but not for conversion to integer. And one that would permit "2e1" for conversion to float, but not to any other data type.