我如何在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中进行数字格式化?

当前回答

这里是另一个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

其他回答

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

输出= 9876543

您可以用列名替换9876543。

2016-08-17 11:22:14

`/* Author: Tsiridis Dimitris */
/* Greek amount format. For the other change the change on replace of '.' & ',' */
CREATE FUNCTION dbo.formatAmount  (
@amtIn as varchar(20)
) RETURNS varchar(20)
AS
BEGIN 

return cast(REPLACE(SUBSTRING(CONVERT(varchar(20), CAST(@amtIn AS money), 1),1,
LEN(CONVERT(varchar(20), CAST(@amtIn AS money), 1))-3), ',','.')
 + replace(RIGHT(CONVERT(varchar(20), CAST(@amtIn AS money), 1),3), '.',',') AS VARCHAR(20))

END

SELECT [geniki].[dbo].[formatAmount]('9888777666555.44')`

2013-02-18 09:59:56

试试上面的金钱技巧，这对于两个或更少有效数字的数值非常有效。我创建了自己的函数来用小数格式化数字:

CREATE FUNCTION [dbo].[fn_FormatWithCommas] 
(
    -- Add the parameters for the function here
    @value varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @WholeNumber varchar(50) = NULL, @Decimal varchar(10) = '', @CharIndex int = charindex('.', @value)

    IF (@CharIndex > 0)
        SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))
    ELSE
        SET @WholeNumber = @value

    IF(LEN(@WholeNumber) > 3)
        SET @WholeNumber = dbo.fn_FormatWithCommas(SUBSTRING(@WholeNumber, 1, LEN(@WholeNumber)-3)) + ',' + RIGHT(@WholeNumber, 3)



    -- Return the result of the function
    RETURN @WholeNumber + @Decimal

END

2011-07-25 15:22:11

请尝试以下查询:

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

右小数点格式:

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

2018-03-30 11:14:05

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

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

2011-09-23 21:05:29

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

推荐文章

最新文章

标签