SQL Server - HOw to convert exponential to number in sqlserver

Asked By The Hunk on 15-Jun-12 01:45 AM
Jitendra Faye replied to The Hunk on 15-Jun-12 01:46 AM
refer this link-

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/8b9ffb98-a155-4943-be08-a106070deeda
Chintan Vaghela replied to The Hunk on 15-Jun-12 01:56 AM


SELECT CAST('9.71501e+012' AS float)
Mayank Tripathi replied to The Hunk on 15-Jun-12 07:32 AM
Within SQL Server the max precision (or "length") of a number is 38.
So, you could use query:
                                      SELECT convert(numeric(38,0),cast(@t AS float))

But you have to be careful for several reasons:
1) if you try to convert numbers with negative exponent, the conversion from above will return Zero.
2) This conversion will not give you the exact number due to internal rounding errors.
Example: '3.733241996799E+27' will return fine but '3.733241996799E+36' will be different.
3) The conversion will fail for any number larger than 10^38–1.

So, you need to decide what you're looking for:
a) get rid of the varchar column (just change to float, but still have exponential notation)
b) change it to numeric values with the risk of getting results that are slightly off (or require additional rounding routine) and the risk of not being able to convert all values or
c) maybe you have a different requirement...