Convert exponential Number to non-exponential numbers...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Convert exponential Number to non-exponential...

Convert exponential Number to non-exponential numbers...

Rate Topic

Display Mode

Topic Options

Author

Message

sandhya_vemulla

Posted Monday, December 21, 2009 8:06 PM

Old 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

spaghettidba

Posted Tuesday, December 22, 2009 1:00 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

First Google entry for "exponential notation":

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

Regards
Gianluca

Get your two-cent-answer quickly
The Spaghetti DBA

Post #837770

LutzM

Posted Tuesday, December 22, 2009 1:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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 »

Permissions