Log in  ::  Register  ::  Not logged in

## Convert exponential Number to non-exponential numbers...

 Author Message sandhya_vemulla Old Hand 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+32Please help me out.thank u sandy Gianluca Sartori SSCrazy Eights Group: General Forum Members Points: 8722 Visits: 13344 First Google entry for "exponential notation":http://en.wikipedia.org/wiki/Scientific_notationRegardsGianluca --Gianluca SartoriHow to post T-SQL questionsspaghettidba.com@spaghettidba LutzM SSCrazy Eights 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 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 manifbest SSC Journeyman 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+431.72733032835877E+621.24376246415887E+736.61741630782285E+846.61741630782285E+842.60152756894243E+97Thanks and Regards,Manifbest ChrisM@Work SSChampion 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+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 manifbest SSC Journeyman 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+2532.60152756894243E+2702.60152756894243E+502.60152756894243E+273that is my main concern.If any help, its very thankful.Regards,Manifbest ChrisM@Work SSChampion 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 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