Convert exponential Number to non-exponential numbers...

Question

Post reply

Convert exponential Number to non-exponential numbers...

sandhya_vemulla

Say Hey Kid

Points: 696
More actions
December 21, 2009 at 8:06 pm

#207664

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

First Google entry for "exponential notation":

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

Regards

Gianluca

-- Gianluca Sartori

LutzM SSC Guru Points: 107049 More actions · Answer 2

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

manifbest SSC Veteran Points: 241 More actions · Answer 3

Hello All,

I want to find out the standard deviation from the following value but it gives me error because i am using STDEV function from the MSSQL.

My data are in the following format.

1.34405857090807E+43

1.72733032835877E+62

1.24376246415887E+73

6.61741630782285E+84

2.60152756894243E+97

Thanks and Regards,

Manifbest

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

manifbest (11/20/2014)
Hello All,
I want to find out the standard deviation from the following value but it gives me error because i am using STDEV function from the MSSQL.
My data are in the following format.
1.34405857090807E+43
1.72733032835877E+62
1.24376246415887E+73
6.61741630782285E+84
6.61741630782285E+84
2.60152756894243E+97
Thanks and Regards,
Manifbest

;WITH MyBoats (MyBoat) AS (

SELECT CAST('1.34405857090807E+43' AS VARCHAR(20)) UNION ALL

SELECT '1.72733032835877E+62' UNION ALL

SELECT '1.24376246415887E+73' UNION ALL

SELECT '6.61741630782285E+84' UNION ALL

SELECT '2.60152756894243E+97'

)

SELECT

STDEV(CAST(MyBoat AS FLOAT)),

MIN(CAST(MyBoat AS FLOAT)),

MAX(CAST(MyBoat AS FLOAT)),

AVG(CAST(MyBoat AS FLOAT))

FROM MyBoats

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

manifbest SSC Veteran Points: 241 More actions · Answer 5

Thanks for your response .

But my concern is not about for only 10 to 20 records.

My data can be of the following format.

2.60152756894243E+253

2.60152756894243E+270

2.60152756894243E+50

2.60152756894243E+273

that is my main concern.

If any help, its very thankful.

Regards,

Manifbest

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

The restrictions of the FLOAT datatype are specified here. The numbers you've posted look ok, however I'd recommend you check your data for elements which may be out of range.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden