在SQL中将指数值转换为实际值 [英] show the exponential values into actual values in sql

问题描述

大家好，

我想将指数值显示为sql中的实际值(例如3.43881e + 007)

问候
Hymz

Hi all,

I wanted to show the exponential values into actual values in sql(eg 3.43881e+007)

regards
Hymz

推荐答案

hi，

请尝试这个.

在SQL Server中，数字的最大精度(或长度")为38.
因此，您可以使用

Please try this.

Within SQL Server the max precision (or "length") of a number is 38.
So, you could use

SELECT convert(numeric(38,0),cast(@value AS float))

但是出于以下几个原因，您必须要小心:
1)如果您尝试转换具有负指数的数字，则上面的转换将返回零.
2)由于内部舍入错误，此转换将无法为您提供确切的数字.
例如:"3.733241996799E + 27"将返回罚款，但"3.733241996799E + 36"将有所不同.
3)任何大于10 ^ 38–1的数字都将转换失败.

因此，您需要确定要寻找的内容:
a)删除varchar列(只是更改为float，但仍具有指数符号)
b)将其更改为数值，可能会导致结果略有偏离(或需要附加的舍入例程)，并且可能无法转换所有值或
c)也许您有不同的要求...

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...

这篇关于在SQL中将指数值转换为实际值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！