Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert exponential Number to non-exponential numbers... Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 8:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 8:25 PM
Points: 372, Visits: 25
Hi

Having one issue in my databas. I have one column which is defined as VARCHAR(100) and it is currently holding large amount fields. I know this is not the best design so there is budget to convert all such columns to based on the data.

But for now i would like to know how to Convert exponential Number to non-exponential numbers...
Ex: one of the column has 3.733241996799E+32


Please help me out.

thank u
sandy
Post #837704
Posted Tuesday, December 22, 2009 1:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 4,419, Visits: 10,725
First Google entry for "exponential notation":

http://en.wikipedia.org/wiki/Scientific_notation

Regards
Gianluca


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #837770
Posted Tuesday, December 22, 2009 1:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
Within SQL Server the max precision (or "length") of a number is 38.
So, you could use
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #837773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse