SQLServerCentral » SQL Server 2008 » SQL Server 2008 - General » Convert exponential Number to non-exponential numbers...InstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralThu, 19 Jan 2017 16:37:35 GMT20Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost837704.aspxHi
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
sandyFri, 21 Nov 2014 06:00:57 GMTsandhya_vemullaRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost1637121.aspxThe restrictions of the FLOAT datatype are specified [url=http://msdn.microsoft.com/en-GB/library/ms173773.aspx]here[/url]. The numbers you've posted look ok, however I'd recommend you check your data for elements which may be out of range.Fri, 21 Nov 2014 06:00:57 GMTChrisM@WorkRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost1637018.aspxThanks 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,
ManifbestThu, 20 Nov 2014 22:59:48 GMTmanifbestRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost1636777.aspx[quote][b]manifbest (11/20/2014)[/b][hr]Hello All,
I want to find out the [b]standard deviation[/b] from the following value but it gives me error because i am using [b]STDEV[/b] 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[/quote]
[code="sql"];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[/code]Thu, 20 Nov 2014 05:56:17 GMTChrisM@WorkRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost1636738.aspxHello All,
I want to find out the [b]standard deviation[/b] from the following value but it gives me error because i am using [b]STDEV[/b] 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,
ManifbestThu, 20 Nov 2014 04:28:09 GMTmanifbestRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost837773.aspxWithin SQL Server the max precision (or "length") of a number is 38.
So, you could use
[code="sql"]
SELECT convert(numeric(38,0),cast(@t AS float))
[/code]
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...Tue, 22 Dec 2009 01:16:17 GMTLutzMRE: Convert exponential Number to non-exponential numbers...https://www.sqlservercentral.com/Forums/FindPost837770.aspxFirst Google entry for "exponential notation":
[url]http://en.wikipedia.org/wiki/Scientific_notation[/url]
Regards
GianlucaTue, 22 Dec 2009 01:00:47 GMTspaghettidba