create function fn_testnum(@m money) returns varchar(20) as begin declare @s varchar(20),@r varchar(20) set @s=reverse(ltrim(ceiling(cast(@m as dec(28,4))))) while len(@s)>0 select @r=isnull(@r+',','')+left(@s,3), @s=stuff(@s,1,3,'') return reverse(@r) end godeclare @m money set @m=12345.67 select dbo.fn_testnum(@m) /* -------------------- 12,346(1 行受影响) */ set @m=1234566545.67 select dbo.fn_testnum(@m) /* -------------------- 1,234,566,546(1 行受影响) */
还是不要用sql做
returns varchar(20)
as
begin
declare @s varchar(20),@r varchar(20)
set @s=reverse(ltrim(ceiling(cast(@m as dec(28,4)))))
while len(@s)>0
select @r=isnull(@r+',','')+left(@s,3),
@s=stuff(@s,1,3,'')
return reverse(@r)
end
godeclare @m money
set @m=12345.67
select dbo.fn_testnum(@m)
/*
--------------------
12,346(1 行受影响)
*/
set @m=1234566545.67
select dbo.fn_testnum(@m)
/*
--------------------
1,234,566,546(1 行受影响)
*/
insert into tb values(12345.67)
goselect convert(varchar , l1 , 1) from tbdrop table tb/*
------------------------------
12,345.67(所影响的行数为 1 行)
*/
--去掉小数位
select reverse(stuff(reverse(convert(varchar,convert(money,12345.67),1)),1,3,''))
returns varchar(20)
as
begin
declare @s varchar(20),@r varchar(20)
set @s=reverse(ltrim(cast(@m as dec(28,0)))) --这里去掉ceiling,我原以为是全舍入的需求
while len(@s)>0
select @r=isnull(@r+',','')+left(@s,3),
@s=stuff(@s,1,3,'')
return reverse(@r)
end
godeclare @m money
set @m=12345.67
select dbo.fn_testnum(@m)
/*
--------------------
12,346(1 行受影响)
*/
set @m=1234566545.67
select dbo.fn_testnum(@m)
/*
--------------------
1,234,566,546(1 行受影响)
*/
select '$'+convert(varchar(256),CAST('1600000000' AS money),1)
insert into tb values(12345.67)
insert into tb values(12334245.67)
insert into tb values(12334534545.67)goselect convert(varchar , l1 , 1) from tbdrop table tb/*
------------------------------
12,345.67
12,334,245.67
12,334,534,545.67(所影响的行数为 3 行)
*/