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

我正在运行一些管理查询，并在SQL Server 2008中编译sp_spaceused的结果，以查看数据库中一些表的数据/索引空间比率。当然，我得到了各种各样的大数字在结果中，我的眼睛开始掩盖。如果我可以用逗号格式化所有这些数字(987654321变成987,654321)，那将非常方便。有趣的是，在我使用SQL Server的这么多年里，这个问题从来没有出现过，因为大多数时候我都会在表示层进行格式化，但在这种情况下，T-SQL导致SSMS 是表示。

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

583009 次浏览
小开

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

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

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

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
小开

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

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

这里是另一个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
小开 
`/* 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')`
小开

对于SQL Server 2012+实现，您将能够使用格式将字符串格式应用于非字符串数据类型。

在最初的问题中，用户要求能够使用逗号作为千位分隔符。在封闭为重复题中，用户询问了如何应用货币格式。下面的查询显示了如何执行这两个任务。它还演示了区域性的应用，以使其成为更通用的解决方案(解决Tsiridis Dimitris应用希腊特殊格式的功能)

-- FORMAT
-- http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx
-- FORMAT does not do conversion, that's the domain of cast/convert/parse etc
-- Only accepts numeric and date/time data types for formatting.
--
-- Formatting Types
-- http://msdn.microsoft.com/en-us/library/26etazsy.aspx


-- Standard numeric format strings
-- http://msdn.microsoft.com/en-us/library/dwhawy9k.aspx
SELECT
-- c => currency
-- n => numeric
FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
(
-- Language culture names
-- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
VALUES
('en-US')
,   ('en-GB')
,   ('ja-JP')
,   ('Ro-RO')
,   ('el-GR')
) C (culture);

SQLFiddle为上述

小开

这里是一个标量函数，我正在使用，在前面的例子(上面)修复了一些错误，也处理十进制值(到指定的数字#)(编辑也工作与0 &负数)。另外需要注意的是，上面的转换为money方法仅限于money数据类型的大小，并且不适用于4(或更多)位小数。这种方法确实更简单，但不太灵活。

CREATE FUNCTION [dbo].[fnNumericWithCommas](@num decimal(38, 18), @decimals int = 4) RETURNS varchar(44) AS
BEGIN
DECLARE @ret varchar(44)


DECLARE @negative bit; SET @negative = CASE WHEN @num < 0 THEN 1 ELSE 0 END


SET @num = abs(round(@num, @decimals)) -- round the value to the number of decimals desired
DECLARE @decValue varchar(18); SET @decValue = substring(ltrim(@num - round(@num, 0, 1)) + '000000000000000000', 3, @decimals)
SET @num = round(@num, 0, 1) -- truncate the incoming number of any decimals
WHILE @num > 0 BEGIN
SET @ret = str(@num % 1000, 3, 0) + isnull(','+@ret, '')
SET @num = round(@num / 1000, 0, 1)
END
SET @ret = isnull(replace(ltrim(@ret), ' ', '0'), '0') + '.' + @decValue
IF (@negative = 1) SET @ret = '-' + @ret


RETURN @ret
END


GO
小开

另一个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
小开
最佳答案

在SQL Server 2012及更高版本中，这将用逗号格式化一个数字:

select format([Number], 'N0')

你也可以将0改为你想要的小数位数。

小开 
/*
#------------------------------------------------------------------------#
#            SQL Query Script                                            #
#            ----------------                                            #
# Funcion.:  dbo.fn_nDerecha ( Numero, Pos_Enteros, Pos_Decimales )      #
#    Numero        : es el Numero o Valor a formatear                    #
#    Pos_Enteros   : es la cantidad posiciones para Enteros              #
#    Pos_Decimales : es la cantidad posiciones para Decimales            #
#                                                                        #
# OBJETIVO:  Formatear los Numeros con Coma y Justificado a la Derecha   #
#  Por Ejemplo:                                                          #
#   dbo.fn_nDerecha ( Numero, 9, 2 )         Resultado = ---,---,--9.99  #
#               dado  Numero = 1234.56       Resultado =       1,234.56  #
#               dado  Numero = -1.56         Resultado =          -1.56  #
#               dado  Numero = -53783423.56  Resultado = -53,783,423.56  #
#                                                                        #
# Autor...:  Francisco Eugenio Cabrera Perez                             #
# Fecha...:  Noviembre 25, 2015                                          #
# Pais....:  Republica Dominicana                                        #
#------------------------------------------------------------------------#
*/






CREATE FUNCTION [dbo].[fn_nDerecha]
(
-- Agregue Argumentos, para personalizar la funcion a su conveniencia
@Numero_str    varchar(max)
,@Pos_Enteros   int
,@Pos_Decimales int
)
RETURNS varchar(max)
AS
BEGIN
--  Declare la variable del RETURN aqui, en este caso es RESULT
declare @RESULTADO varchar(max)
set     @RESULTADO = '****'


-----------------------------------------------  --
declare @Numero_num numeric(28,12)
set     @Numero_num =
(
case when isnumeric(@Numero_str) = 0
then 0
else round (convert( numeric(28,12), @Numero_str), @Pos_Decimales)
end
)
--  -----------------------------------------------  --
--  Aumenta @Pos_Enteros de @RESULTADO,
--      si las posiciones de Enteros del dato @Numero_str es Mayor...
--
declare   @Num_Pos_Ent int
set       @Num_Pos_Ent = len ( convert( varchar, convert(int, abs(@Numero_num) ) ) )
--
declare   @Pos_Ent_Mas int
set       @Pos_Ent_Mas =
(
case when @Num_Pos_Ent > @Pos_Enteros
then @Num_Pos_Ent - @Pos_Enteros
else 0
end
)
set       @Pos_Enteros = @Pos_Enteros + @Pos_Ent_Mas
--
--  -----------------------------------------------  --
declare @p_Signo_ctd       int
set     @p_Signo_ctd       = (case when @Numero_num < 1 then 1 else 0 end)
--
declare @p_Comas_ctd       int
set     @p_Comas_ctd       = ( @Pos_Enteros - 1 ) / 3
--
declare @p_Punto_ctd       int
set     @p_Punto_ctd       = (case when @Pos_Decimales > 0 then 1 else 0 end)
--
declare @p_input_Longitud  int
set     @p_input_Longitud  = ( @p_Signo_ctd + @Pos_Enteros ) +
@p_Punto_ctd + @Pos_Decimales
--
declare @p_output_Longitud int
set     @p_output_Longitud = ( @p_Signo_ctd + @Pos_Enteros   + @p_Comas_ctd )
+ ( @p_Punto_ctd + @Pos_Decimales )
--
--  ===================================================================  --




declare @Valor_str varchar(max)
set     @Valor_str = str(@Numero_num, @p_input_Longitud, @Pos_Decimales)


declare @V_Ent_str varchar(max)
set     @V_Ent_str =
(case when @Pos_Decimales > 0
then substring( @Valor_str, 0, charindex('.', @Valor_str, 0) )
else            @Valor_str end)
--
declare @V_Dec_str varchar(max)
set     @V_Dec_str =
(case when @Pos_Decimales > 0
then '.' + right(@Valor_str, @Pos_Decimales)
else '' end)
--
set @V_Ent_str = convert(VARCHAR, convert(money, @V_Ent_str), 1)
set @V_Ent_str = substring( @V_Ent_str, 0, charindex('.', @V_Ent_str, 0) )
--




set @RESULTADO    = @V_Ent_str + @V_Dec_str
--
set @RESULTADO = ( replicate( ' ', @p_output_Longitud - len(@RESULTADO) ) + @RESULTADO )
--


--  ===================================================================  -

-- =================================================================== -

  RETURN @RESULTADO
END


--  ===================================================================  --
< p > / * 这个函数需要3个参数:第一个参数是@Numero_str，它将数字作为数据输入，其他2个参数指定如何格式化输出信息，这些参数是@Pos_Enteros和@Pos_Decimales，它们指定要为作为输入参数传递的数字显示多少个整数和小数。 * / < / p >
小开

演示1

演示如何添加逗号:

PRINT FORMATMESSAGE('The number is: %s', format(5000000, '#,##0'))
-- Output
The number is: 5,000,000

演示2

演示逗号和小数点。注意，如果需要，它会四舍五入最后一位数字。

PRINT FORMATMESSAGE('The number is: %s', format(5000000.759145678, '#,##0.00'))
-- Output
The number is: 5,000,000.76

兼容性

SQL Server 2012+。

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

输出= 9876543

您可以用列名替换9876543。

小开

这属于菲尔·亨特的答案的注释，但可惜我没有代表。

剥离"。在数字字符串的末尾加上00"，parsename就非常方便了。 它标记以句点分隔的字符串并返回指定的元素，从最右边的标记作为元素1开始 

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

收益率“987654321”

小开

请尝试以下查询:

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

右小数点格式:

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

对于2012年以前不包含FORMAT函数的SQL Server，创建如下函数:

CREATE FUNCTION FormatCurrency(@value numeric(30,2))
RETURNS varchar(50)
AS
BEGIN
DECLARE @NumAsChar VARCHAR(50)
SET @NumAsChar = '$' + CONVERT(varchar(50), CAST(@Value AS money),1)
RETURN @NumAsChar
END
< p >选择dbo.FormatCurrency (12345678) 返回12345678美元< / p >

如果你只想要逗号，就去掉$。


提交答案