--创建函数(原创: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 */
[MSSQL]将数字转换千分位分隔形式
最新推荐文章于 2022-06-30 16:14:57 发布