Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert exponential Number to non-exponential numbers...


Convert exponential Number to non-exponential numbers...

Author
Message
sandhya_vemulla
sandhya_vemulla
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 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
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8722 Visits: 13344
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
LutzM
LutzM
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9119 Visits: 13559
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
manifbest
manifbest
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 284
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
ChrisM@Work
ChrisM@Work
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13547 Visits: 19409
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 '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
Exploring Recursive CTEs by Example Dwain Camps
manifbest
manifbest
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 284
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
ChrisM@Work
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13547 Visits: 19409
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search