27,579
社区成员
发帖
与我相关
我的任务
分享
alter function fn_test(
@s varchar(100),
@i int
)
returns float
as
begin
declare @f float
set @f=cast(@s as float)
declare @d int
set @d=0
declare @n int
if @f>0
set @n=1
else
set @n=-1
set @f=abs(@f)
while @f>1
begin
set @d=@d+1
set @f=@f/10
end
while @f<0.1
begin
set @d=@d-1
set @f=@f*10
end
return @n*ROUND(@f,@i)*power(cast(10 as float),@d)
end
go
create function fn_test(
@s varchar(100),
@i int
)
returns float
as
begin
declare @f float
set @f=cast(@s as float)
declare @d int
set @d=0
while @f>1
begin
set @d=@d+1
set @f=@f/10
end
return ROUND(@f,@i)*power(10,@d)
end
go
select dbo.fn_test('123456',4)
--123500
select dbo.fn_test('1.23456',4)
--1.235
select dbo.fn_test('0.00123456',4)
--0.0012
create table tb(num float)
insert tb
select 12345 union all
select 123456.12 union all
select 2222.88 union all
select 123.444 union all
select 0.43 union all
select -0.444555
--保留4位有效数字
select case when charindex('.',num)=0 then left(num,4)
else case when abs(num)<1 then
case when len(abs(num))>6 then rtrim(round(num,4))
else rtrim(round(num,4))+replicate('0',6-len(abs(num)))
end
else rtrim(round(num,5-charindex('.',abs(num))))
end
end
from tb
/*
1234
1234
2223
123.4
0.4300
-0.4446
*/
drop table tb
DECLARE @S VARCHAR(10)
SET @S='10'
SELECT CAST(@S AS DECIMAL(10,4))
/*
10.0000
*/
楼主研究 str 函数
declare @n int
set @n=10
select rtrim(ltrim(str(@n,20,4)))
结果:
10.0000