Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert exponential Number to non-exponential numbers... Rate Topic Display Mode Topic Options
Author
 Message
 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+32Please help me out.thank u sandy
Post #837704
 Posted Tuesday, December 22, 2009 1:00 AM
 SSCertifiable Group: General Forum Members Last Login: Tuesday, November 22, 2016 6:32 AM Points: 5,519, Visits: 13,287
 First Google entry for "exponential notation":http://en.wikipedia.org/wiki/Scientific_notationRegardsGianluca --Gianluca SartoriHow to post T-SQL questionsspaghettidba.com@spaghettidba
Post #837770
 Posted Tuesday, December 22, 2009 1:16 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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 orc) maybe you have a different requirement... LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #837773
 Posted Thursday, November 20, 2014 4:28 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, October 13, 2016 11:37 AM Points: 81, 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+431.72733032835877E+621.24376246415887E+736.61741630782285E+846.61741630782285E+842.60152756894243E+97Thanks and Regards,Manifbest
Post #1636738
 Posted Thursday, November 20, 2014 5:56 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 9:41 AM Points: 8,605, Visits: 18,775
 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+431.72733032835877E+621.24376246415887E+736.61741630782285E+846.61741630782285E+842.60152756894243E+97Thanks 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1636777
 Posted Thursday, November 20, 2014 10:59 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, October 13, 2016 11:37 AM Points: 81, 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+2532.60152756894243E+2702.60152756894243E+502.60152756894243E+273that is my main concern.If any help, its very thankful.Regards,Manifbest
Post #1637018
 Posted Friday, November 21, 2014 6:00 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 9:41 AM Points: 8,605, Visits: 18,775
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1637121

 Permissions