Convert exponential Number to non-exponential numbers...

 Author Message sandhya_vemulla SSC-Addicted Group: General Forum Members Points: 428 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 SSC-Dedicated Group: General Forum Members Points: 38851 Visits: 13367 First Google entry for "exponential notation":http://en.wikipedia.org/wiki/Scientific_notationRegardsGianluca --Gianluca SartoriHow to post T-SQL questionsspaghettidba.com@spaghettidba LutzM SSC-Dedicated Group: General Forum Members Points: 38179 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-Enthusiastic Group: General Forum Members Points: 156 Visits: 299 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 SSC Guru Group: General Forum Members Points: 65908 Visits: 20214 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-Enthusiastic Group: General Forum Members Points: 156 Visits: 299 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 SSC Guru Group: General Forum Members Points: 65908 Visits: 20214 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