SQL Server 用逗号格式化小数位 [英] SQL Server format decimal places with commas

问题描述

如何将十进制值转换为包含逗号?

How can I convert the decimal values to have some commas?

this 对我有帮助.但我的问题是小数位只设置为 2..我希望小数为 2、3 或 4..example

this Helps me. But my problem the decimal places are set to 2 only..I want the decimal to be 2, 3, or 4..example

1,234.123   or    1,234.12345

我试过了

convert(varchar, convert(decimal(18, 4), 1234.1234567), 1) 

输出:1234.1234

Output : 1234.1234

没有逗号.但如果我用钱，小数点只有 2

There is no comma. But if I use money the decimal are 2 only

convert(varchar, convert(money, 1234.1234567), 1)

convert(varchar, convert(money, 1234.1234567), 1)

输出:1,234.12

Output : 1,234.12

推荐答案

不认为这是一个好主意...

without considering this to be a good idea...

select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))

功能

-- Author:      bummi
-- Create date: 20121106
CREATE FUNCTION F_AddThousandSeparators(@NumStr varchar(50)) 
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int

Select @i=CHARINDEX('.',@NumStr)
if @i=0 
    begin
    set @i=LEN(@NumStr)
    Set @Outstr=''
    end
else
    begin   
     Set @Outstr=SUBSTRING(@NUmStr,@i,50)
     Set @i=@i -1
    end 


Set @run=0

While @i>0
    begin
      if @Run=3
        begin
          Set @Outstr=','+@Outstr
          Set @run=0
        end
      Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr  
      Set @i=@i-1
      Set @run=@run + 1     
    end

    RETURN @OutStr

END
GO

这篇关于SQL Server 用逗号格式化小数位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！