-----------建一個函數完成------------- Create Function trun(@i decimal(18,6)) returns varchar(30) begin declare @s varchar(30) set @s=cast(@i as varchar(30)) while len(@s)>0 begin if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as int)<>0 begin set @s=substring(rtrim(@s),1,len(@s)-1) break end set @s=substring(rtrim(@s),1,len(@s)-1) end return(@s) end ----------------------------結束函數-----------select dbo.trun(12.26000) ----------測試函數 ---------------輸出 12.26 drop function trun
如果没有小数点,trun是不可以用的!
函數修改一點點就行了 -----------建一個函數完成------------- Create Function trun(@i decimal(18,6)) returns varchar(30) begin declare @s varchar(30) set @s=cast(@i as varchar(30)) while len(@s)>0 begin if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as varchar)<>'0' begin if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as varchar)='.' set @s=substring(rtrim(@s),1,len(@s)-2) else set @s=substring(rtrim(@s),1,len(@s)-1) break end set @s=substring(rtrim(@s),1,len(@s)-1) end return(@s) end ----------------------------結束函數-----------select dbo.trun(120.010) ----------測試函數 select dbo.trun(1200.0000) ----------測試函數 ---------------輸出drop function trun
print cast(2.5000000 as float)
convert(decimal(18,3),进价) as 进价 3可以改你需要的位数吧,不知道这个可以不?
Create Function trun(@i decimal(18,6))
returns varchar(30)
begin
declare @s varchar(30)
set @s=cast(@i as varchar(30))
while len(@s)>0
begin
if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as int)<>0
begin
set @s=substring(rtrim(@s),1,len(@s)-1)
break
end
set @s=substring(rtrim(@s),1,len(@s)-1)
end
return(@s)
end
----------------------------結束函數-----------select dbo.trun(12.26000) ----------測試函數
---------------輸出
12.26
drop function trun
-----------建一個函數完成-------------
Create Function trun(@i decimal(18,6))
returns varchar(30)
begin
declare @s varchar(30)
set @s=cast(@i as varchar(30))
while len(@s)>0
begin
if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as varchar)<>'0'
begin
if cast(substring(rtrim(@s),len(rtrim(@s))-1,1) as varchar)='.'
set @s=substring(rtrim(@s),1,len(@s)-2)
else
set @s=substring(rtrim(@s),1,len(@s)-1)
break
end
set @s=substring(rtrim(@s),1,len(@s)-1)
end
return(@s)
end
----------------------------結束函數-----------select dbo.trun(120.010) ----------測試函數
select dbo.trun(1200.0000) ----------測試函數
---------------輸出drop function trun
3可以改你需要的位数吧,不知道这个可以不?