34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(l1 Money)
insert into tb values(12345.67)
insert into tb values(12334245.67)
insert into tb values(12334534545.67)
go
select convert(varchar , l1 , 1) from tb
drop table tb
/*
------------------------------
12,345.67
12,334,245.67
12,334,534,545.67
(所影响的行数为 3 行)
*/
select '$'+convert(varchar(256),CAST('1600000000' AS money),1)
create function fn_testnum(@m money)
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
go
declare @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 行受影响)
*/
create table tb(l1 Money)
insert into tb values(12345.67)
go
select convert(varchar , l1 , 1) from tb
drop table tb
/*
------------------------------
12,345.67
(所影响的行数为 1 行)
*/
select replace(convert(varchar,convert(money,round(12345.67, 0)),1), '.00', '')
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
go
declare @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,convert(money,round(12345.67, 0)),1)