/* 取数字 num 的前 n位 不够的用0补 例如 输入 123456 , 2 输出 120000 输入 123456 , 10 输出 123456.000应该是这样的吧~` 关注中...... */ create function f_num(@num float,@n int) returns varchar(50) as begin if @num<1 return(left(cast(@num as varchar)+replicate('0',@n),@n+2)) if charindex('.',@num)=0 return(case when len(@num)<@n then cast(@num as varchar)+'.'+replicate('0',@n-len(@num)) else left(cast(@num as varchar),@n)+replicate('0',len(@num)-@n) end) return(case when @n<charindex('.',@num) then left(@num,@n)+replicate('0',charindex('.',@num)-@n-1) when len(@num)-1<@n then cast(@num as varchar)+replicate('0',@n-len(@num)+1) else left(@num,@n+1) end) end go--调用 select dbo.f_num(1068,5),dbo.f_num(0.0765,6),dbo.f_num(1.655,5) --结果:1068.0 0.076500 1.6550select dbo.f_num(1068,3),dbo.f_num(1.072265,5),dbo.f_num(165.5,2) --结果: 1060 1.0722 160 go--删除测试 drop function f_num
http://community.csdn.net/Expert/topic/3956/3956412.xml?temp=.6920587检查一下是否符合要求.
取数字 num 的前 n位 不够的用0补
例如 输入 123456 , 2 输出 120000
输入 123456 , 10 输出 123456.000应该是这样的吧~`
关注中......
*/
create function f_num(@num float,@n int)
returns varchar(50)
as
begin
if @num<1 return(left(cast(@num as varchar)+replicate('0',@n),@n+2)) if charindex('.',@num)=0
return(case
when len(@num)<@n then cast(@num as varchar)+'.'+replicate('0',@n-len(@num))
else left(cast(@num as varchar),@n)+replicate('0',len(@num)-@n) end) return(case
when @n<charindex('.',@num) then left(@num,@n)+replicate('0',charindex('.',@num)-@n-1)
when len(@num)-1<@n then cast(@num as varchar)+replicate('0',@n-len(@num)+1)
else left(@num,@n+1) end)
end
go--调用
select dbo.f_num(1068,5),dbo.f_num(0.0765,6),dbo.f_num(1.655,5)
--结果:1068.0 0.076500 1.6550select dbo.f_num(1068,3),dbo.f_num(1.072265,5),dbo.f_num(165.5,2)
--结果: 1060 1.0722 160
go--删除测试
drop function f_num