[MSSQL]将数字转换千分位分隔形式

--创建函数(原创：dobear_0922)
create function [dbo].[getformatstring](@dec decimal(28,8), @n int)
returns varchar(32) as
begin
declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32)
if @n!='0' 
begin
set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len <=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left+'.'+@right
end
else
begin
set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len <=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left
end
return @end
end
--测试示例
select [dbo].[getformatstring](2645433,2)
--运行结果
/*
2,645,433.00
*/
--如果小数点后面不需要处理的话，我们可以不用函数直接：
select convert(varchar, convert(money, 2645433), 1)
/*
2,645,433.00
*/