我如何在T-SQL用逗号格式化一个数字?

I'm running some administrative queries and compiling results from sp_spaceused in SQL Server 2008 to look at data/index space ratios of some tables in my database. Of course I am getting all sorts of large numbers in the results and my eyes are starting to gloss over. It would be really convenient if I could format all those numbers with commas (987654321 becomes 987,654,321). Funny that in all the many years I've used SQL Server, this issue has never come up since most of the time I would be doing formatting at the presentation layer, but in this case the T-SQL result in SSMS is the presentation.

我考虑过只创建一个简单的CLR UDF来解决这个问题，但似乎这应该可以在普通的旧T-SQL中完成。所以，我将在这里提出一个问题-如何在香草T-SQL中进行数字格式化?

当前回答

请尝试以下查询:

SELECT FORMAT(987654321,'#,###,##0')

右小数点格式:

SELECT FORMAT(987654321,'#,###,##0.###\,###')

2018-03-30 11:14:05

其他回答

SELECT REPLACE(CONVERT(varchar(20), (CAST(9876543 AS money)), 1), '.00', '')

输出= 9876543

您可以用列名替换9876543。

2016-08-17 11:22:14

另一个UDF，希望足够通用，并且不假设你是否想四舍五入到一个特定的小数点后数位:

CREATE FUNCTION [dbo].[fn_FormatNumber] (@number decimal(38,18))

RETURNS varchar(50)

BEGIN
    -- remove minus sign before applying thousands seperator
    DECLARE @negative bit
    SET @negative = CASE WHEN @number < 0 THEN 1 ELSE 0 END
    SET @number = ABS(@number)

    -- add thousands seperator for every 3 digits to the left of the decimal place
    DECLARE @pos int, @result varchar(50) = CAST(@number AS varchar(50))
    SELECT @pos = CHARINDEX('.', @result)
    WHILE @pos > 4
    BEGIN
        SET @result = STUFF(@result, @pos-3, 0, ',')
        SELECT @pos = CHARINDEX(',', @result)
    END

    -- remove trailing zeros
    WHILE RIGHT(@result, 1) = '0'
        SET @result = LEFT(@result, LEN(@result)-1)
    -- remove decimal place if not required
    IF RIGHT(@result, 1) = '.'
        SET @result = LEFT(@result, LEN(@result)-1)

    IF @negative = 1
        SET @result = '-' + @result

    RETURN @result
END

2015-05-28 05:04:10

我建议用Replace代替Substring来避免字符串长度问题:

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')

2011-09-23 21:05:29

虽然我同意包括OP在内的所有人的观点，即格式化应该在表示层完成，但这种格式化可以在T-SQL中通过转换为money然后转换为varchar来完成。不过，这确实包括后面的小数，可以用SUBSTRING循环。

SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

2010-12-07 14:02:44

这里是另一个t-sql UDF

CREATE FUNCTION dbo.Format(@num int)
returns varChar(30)
As
Begin
Declare @out varChar(30) = ''

  while @num > 0 Begin
      Set @out = str(@num % 1000, 3, 0) + Coalesce(','+@out, '')
      Set @num = @num / 1000
  End
  Return @out
End

2012-09-06 13:14:28

我如何在T-SQL用逗号格式化一个数字?

推荐文章

最新文章

标签