再問怎麼把一個類型字段的數據轉換成字符串,要求最多要保留6位小數,如果沒有6位小數的不需要0零填充,謝謝!!!

[laker_tmj]

再問怎麼把一個類型字段的數據轉換成字符串
要求最多要保留6位小數,如果沒有6位小數的不需要0零填充,謝謝!!!

如
0.10500000000000001 0.105
0.22900000000000001 0.229
0.0528 0.0528
9.7999999999999997E-3 0.009
0.0276 0.0276
19.731406689 19.731407
6.9000000000000006E-2 0.07

[laker_tmj]

up

[dulei115]

cast(cast(round(9.7999999999999997E-3, 6) as float) as varchar(20))

[dulei115]

或者
cast(cast(round(9.7999999999999997E-3, 6) as real) as varchar(20))


cast(round(9.7999999999999997E-3, 6) as real)

cast(round(9.7999999999999997E-3, 6) as float)

[laker_tmj]

to dulei115() 以下語句不行,

SELECT cast(cast(round(6.9999999999999994E-5, 6) as float) as varchar(20))

SELECT cast(cast(round(6.9999999999999994E-5, 6) as real) as varchar(20))

[dulei115]

刚刚测试了一下，发现是存在这个问题，
cast(round(6.9999999999999994E-5, 6) as float)
这样的方法，对0.00...这样的数，当紧接小数点后面的0的个数>=4是就会以科学计数法显示，我看你还是写函数吧，
首先转换为numeric(20, 6)，再转换为字符，然后去掉尾部的0
create formatfloat(@num float)
returns varchar(50)
as
begin
declare @s varchar(50)
set @s = cast(cast(round(@num, 6) as numeric(20, 6)) as varchar(50))
while charindex('0', @s, len(@s)) = len(@s)
set @s = left(@s, len(@s) - 1)
return @s
end