SQL Server 中的科学数字格式

发布于 2025-01-11 13:35:40 字数 651 浏览 0 评论 0原文

SELECT
    fld AS val, 
    CONVERT(VARCHAR, FORMAT(fld, 'E2')) AS _2decimal, 
    CONVERT(VARCHAR, FORMAT(fld, 'E3')) AS _3decimal

结果：

val	_2decimal	_3decimal
0.0000007018	7.02E-006	7.018E-006

FORMAT 函数在 SQL Server 上可以很好地完成这项工作（截至 2012 年）。

我正在寻找执行相同工作但使用旧版本 SQL Server 的函数。

谢谢

原文

SELECT
    fld AS val, 
    CONVERT(VARCHAR, FORMAT(fld, 'E2')) AS _2decimal, 
    CONVERT(VARCHAR, FORMAT(fld, 'E3')) AS _3decimal

Result:

val	_2decimal	_3decimal
0.0000007018	7.02E-006	7.018E-006

The FORMAT function does the job fine with SQL Server (as of 2012).

I'm looking for the functions doing the same job but with older versions of SQL Server.

Thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

温折酒 2025-01-18 13:35:40

使用LEFT & 右 & ROUND 它可以生成该格式，无需 FORMAT。

选择 fld
, CONCAT(圆形(左(转换(VARCHAR(20),转换(FLOAT,fld),1),9),2), 上(右(转换(VARCHAR(20),转换(FLOAT,fld),1) ,5))) AS _2十进制
, CONCAT(圆形(左(转换(VARCHAR(20),转换(FLOAT,fld),1),9),3), 上(右(转换(VARCHAR(20),转换(FLOAT,fld),1) ,5))) AS _3十进制
从（值
(1.2345E-6), 
(-1.2345E-6), 
(0.0000007018)
) q(fld)

<表类=“s-表”>
<标题>
fld
_2decimal
_3decimal

<正文>
1.2345E-06
1.23E-006
1.234E-006
-1.2345E-06
-1.23E-006
-1.234E-006
7.018E-07
7.02E-007
7.018E-007

测试db<>fiddle 此处

Using LEFT & RIGHT & ROUND it can generate that format without FORMAT.

SELECT fld
, CONCAT(ROUND(LEFT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),9),2), UPPER(RIGHT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),5))) AS _2decimal
, CONCAT(ROUND(LEFT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),9),3), UPPER(RIGHT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),5))) AS _3decimal
FROM (values
(1.2345E-6), 
(-1.2345E-6), 
(0.0000007018)
) q(fld)

fld _2decimal _3decimal
1.2345E-06 1.23E-006 1.234E-006
-1.2345E-06 -1.23E-006 -1.234E-006
7.018E-07 7.02E-007 7.018E-007

fld	_2decimal	_3decimal
1.2345E-06	1.23E-006	1.234E-006
-1.2345E-06	-1.23E-006	-1.234E-006
7.018E-07	7.02E-007	7.018E-007

Test on db<>fiddle here

回复收藏 0 原文

青萝楚歌 2025-01-18 13:35:40

select f, p, f_fmt
from (values (0.0000007018e, 2), (0.0000007018, 3)) as t(f, p)
cross apply (select
    case when sign(f) = -1 then 1 else 0 end,
    p + 2 + case when sign(f) = -1 then 1 else 0 end
) as val0(f_isneg, f_len) cross apply (select
    round(cast(
        left(convert(varchar(24), f, 2), f_len + 1)
        as decimal(18, 16)), p)
) as val1(f_rnd) cross apply (select
        convert(varchar(24),
            case when abs(f_rnd) = 10
                then power(10e, ceiling(log(abs(f)) / log(10))) else f end, 2)
) as val2(f_convert) cross apply (select
    replace(stuff(
        f_convert, 1, 17 + f_isneg,
        left(
            cast(round(cast(
                left(f_convert, f_len + 1)
                as decimal(18, 16)), p) as varchar(24)),
            f_len)), '.', replicate('.', sign(p)))            
) as val3(f_fmt)

如果需要的话，这一切都可以通过cross apply轻松内联。

假设 convert(varchar, float, 2) 返回一个精确到小数点后 15 位的值（记录为“始终为 16”，计算前导数字。）由于它是科学记数法，因此将有一个前导数字和小数位是 +2 发挥作用的地方。如果该值为负数，则字符串会长一个字符。

最棘手的部分是所有舍入行为。负面信号也会带来一个小曲线球。由于四舍五入部分，十四位是最大值。如果您需要 15 个位置，则只需获取 convert(varchar, f, 2) 的原始输出即可。

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=b39ae98e8b301d475941bddec7eaea42

select f, p, f_fmt
from (values (0.0000007018e, 2), (0.0000007018, 3)) as t(f, p)
cross apply (select
    case when sign(f) = -1 then 1 else 0 end,
    p + 2 + case when sign(f) = -1 then 1 else 0 end
) as val0(f_isneg, f_len) cross apply (select
    round(cast(
        left(convert(varchar(24), f, 2), f_len + 1)
        as decimal(18, 16)), p)
) as val1(f_rnd) cross apply (select
        convert(varchar(24),
            case when abs(f_rnd) = 10
                then power(10e, ceiling(log(abs(f)) / log(10))) else f end, 2)
) as val2(f_convert) cross apply (select
    replace(stuff(
        f_convert, 1, 17 + f_isneg,
        left(
            cast(round(cast(
                left(f_convert, f_len + 1)
                as decimal(18, 16)), p) as varchar(24)),
            f_len)), '.', replicate('.', sign(p)))            
) as val3(f_fmt)

This is all easily inlined if necessary with cross apply.

The assumptions are that convert(varchar, float, 2) returns a value with exactly 15 decimal places (which is documented as "always 16" counting the lead digit.) Since it's scientific notation there will be a leading digit and decimal place which is where the +2 comes into play. If the value is negative then the string is one character longer.

The trickiest part is all the rounding behavior. Negative signs throw a small curveball as well. Fourteen places is the maximum because of the rounding portion. If you need 15 places then just grab the original output of convert(varchar, f, 2) instead.

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=b39ae98e8b301d475941bddec7eaea42

回复收藏 0 原文

~没有更多了~