SQL 如何将数字转换为以逗号分隔的数字格式字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6309541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I convert a number to a numeric, comma-separated formatted string?
提问by RLH
Is there an easy way to convert a number (in my case an integer) to a comma separated nvarcharstring?
有没有一种简单的方法可以将数字（在我的情况下是整数）转换为逗号分隔的nvarchar字符串？
For instance, if I had an intvalue of 1000000stored in a field, how can I convert it to an nvarcharstring with the outputted result of "1,000,000"?
例如，如果我有一个int值1000000存储在一个字段中，如何将其转换nvarchar为输出结果为“1,000,000”的字符串？
I could easily write a function to do this but I wanted to be sure there wasn't an easier way involving a call to either CASTor CONVERT.
我可以轻松编写一个函数来执行此操作，但我想确保没有更简单的方法涉及调用CAST或CONVERT。
采纳答案by Jacob Thomas
For SQL Server 2012, or later, an easier solution is to use FORMAT ()Documentation.
EG:
对于SQL Server 2012或更高版本，更简单的解决方案是使用FORMAT ()Documentation。
例如：
SELECT Format(1234567.8, '##,##0')
Results in: 1,234,568
结果是： 1,234,568
回答by Michael Ames
The reason you aren't finding easy examples for how to do this in T-SQL is that it is generally considered bad practice to implement formatting logic in SQL code. RDBMS's simply are not designed for presentation. While it is possible to do some limited formatting, it is almost always better to let the application or user interface handle formatting of this type.
您没有找到有关如何在 T-SQL 中执行此操作的简单示例的原因是，在 SQL 代码中实现格式化逻辑通常被认为是不好的做法。RDBMS 根本不是为演示而设计的。虽然可以进行一些有限的格式化，但让应用程序或用户界面处理这种类型的格式化几乎总是更好。
But if you must(and sometimes we must!) use T-SQL, cast your int to money and convert it to varchar, like this:
但是，如果您必须（有时我们必须！）使用 T-SQL，请将您的 int 转换为 money 并将其转换为 varchar，如下所示：
select convert(varchar,cast(1234567 as money),1)
If you don't want the trailing decimals, do this:
如果您不想要尾随小数，请执行以下操作：
select replace(convert(varchar,cast(1234567 as money),1), '.00','')
Good luck!
祝你好运！
回答by Eddie Kumar
Although formatting belongs to the Presentation layer, SQL Server 2012 and above versions provide FORMAT() function which provides one of the quickest and easiest way to format output. Here are some tips & examples:
尽管格式化属于表示层，但 SQL Server 2012 及以上版本提供了 FORMAT() 函数，该函数提供了一种最快、最简单的格式化输出方法。以下是一些提示和示例：
Syntax: Format( value, format [, culture ] )
语法：Format( value, format [, culture ] )
Returns: Format function returns NVarchar string formatted with the specified format and with optional culture. (Returns NULL for invalid format-string.)
返回： Format 函数返回使用指定格式和可选区域性格式化的 NVarchar 字符串。（对于无效的格式字符串，返回 NULL。）
Note: The Format() function is consistent across CLR / all .NET languages and provides maximum flexibility to generate formatted output.
注意：Format() 函数在 CLR/所有 .NET 语言中是一致的，并为生成格式化输出提供了最大的灵活性。
Following are few format types that can be achieved using this function:
以下是使用此函数可以实现的几种格式类型：
Numeric/Currency formatting- 'C' for currency, 'N' number without currency symbol, 'x' for Hexa-decimals.
Date/Time formatting- 'd' short date, 'D' long date, 'f' short full date/time, 'F' long full date/time, 't' short time, 'T' long time, 'm' month+day, 'y' year+month.
Custom formatting- you can form your own-custom format using certain symbols/characters, such as dd, mm, yyyy etc. (for Dates). hash (#) currency symbols (￡ $ etc.), comma(,) and so on. See examples below.
数字/货币格式- 'C' 代表货币，'N' 数字不带货币符号，'x' 代表十六进制。
日期/时间格式- 'd' 短日期、'D' 长日期、'f' 短完整日期/时间、'F' 长完整日期/时间、't' 短时间、'T' 长时间、'm'月+日，“y”年+月。
自定义格式- 您可以使用某些符号/字符形成自己的自定义格式，例如 dd、mm、yyyy 等（用于日期）。hash (#) 货币符号（￡ $ 等）、逗号（,）等。请参阅下面的示例。
Examples:
例子：
Examples of Built-in Numeric/CurrencyFormats:
内置数字/货币格式示例：
? select FORMAT(1500350.75, 'c', 'en-gb') --> ￡1,500,350.75
? select FORMAT(1500350.75, 'c', 'en-au') --> ,500,350.75
? select FORMAT(1500350.75, 'c', 'en-in') --> ? 15,00,350.75
Examples of Built-in DateFormats:
内置日期格式示例：
? select FORMAT(getdate(), 'd', 'en-gb') --> 20/06/2017
? select FORMAT(getdate(), 'D', 'fr-fr') --> mardi 20 juin 2017
? select FORMAT(getdate(), 'F', 'en-us') --> Tuesday, June 20, 2017 10:41:39 PM
? select FORMAT(getdate(), 'T', 'en-gb') --> 22:42:29
Examples of Custom Formatting:
自定义格式示例：
? select FORMAT(GETDATE(), 'ddd dd/MM/yyyy') --> Tue 20/06/2017
? select FORMAT(GETDATE(), 'dddd dd-MMM-yyyy') --> Tuesday 20-Jun-2017
? select FORMAT(GETDATE(), 'dd.MMMM.yyyy HH:mm:ss') --> 20.June.2017 22:47:20
? select FORMAT(123456789.75,'$#,#.00') --> 3,456,789.75
? select FORMAT(123456789.75,'￡#,#.0') --> ￡123,456,789.8
See MSDNfor more information on FORMAT() function.
有关FORMAT() 函数的更多信息，请参阅MSDN。
For SQL Server 2008 or belowconvert the output to MONEY first then to VARCHAR (passing "1" for the 3rd argument of CONVERT() function to specify the "style"of output-format), e.g.:
对于SQL Server 2008 或更低版本，首先将输出转换为 MONEY，然后转换为 VARCHAR（为 CONVERT() 函数的第三个参数传递“1”以指定输出格式的“样式”），例如：
? select CONVERT(VARCHAR, CONVERT(MONEY, 123456789.75), 1) --> 123,456,789.75
回答by Denis de Bernardy
Not sure it works in tsql, but some platformshave to_char():
不确定它是否适用于 tsql，但某些平台有to_char()：
test=#select to_char(131213211653.78, '9,999,999,999,999.99');
to_char
-----------------------
131,213,211,653.78
test=# select to_char(131213211653.78, '9G999G999G999G999D99');
to_char
-----------------------
131,213,211,653.78
test=# select to_char(485, 'RN');
to_char
-----------------
CDLXXXV
As the example suggests, the format's length needs to match that of the number for best results, so you might want to wrap it in a function (e.g. number_format()) if needed.
如示例所示，格式的长度需要与数字的长度匹配才能获得最佳结果，因此如果需要，您可能希望将其包装在一个函数中（例如 number_format()）。
Converting to money works too, as point out by the other repliers.
正如其他回复者指出的那样，转换为金钱也有效。
test=# select substring(cast(cast(131213211653.78 as money) as varchar) from 2);
substring
--------------------
131,213,211,653.78
回答by Alex K.
Quick & dirty for int to nnn,nnn...
int 到 nnn,nnn 的快速和脏...
declare @i int = 123456789
select replace(convert(varchar(128), cast(@i as money), 1), '.00', '')
>> 123,456,789
回答by djdanlib
You really shouldn't be doing that in SQL - you should be formatting it in the middleware instead. But I recognize that sometimes there is an edge case that requires one to do such a thing.
你真的不应该在 SQL 中这样做 - 你应该在中间件中格式化它。但我承认有时有一种边缘情况需要一个人做这样的事情。
This looks like it might have your answer:
这看起来可能有你的答案：
回答by Hank Freeman
I looked at several of the options. Here are my two favorites, because I needed to round the value.
我看了几个选项。这是我最喜欢的两个，因为我需要对值进行四舍五入。
,DataSizeKB = replace(convert(varchar,Cast(Round(SUM(BigNbr / 0.128),0)as money),1), '.00','')
,DataSizeKB2 = format(Round(SUM(BigNbr / 0.128),0),'##,##0')
-----------------
--- below if the full script where I left DataSizeKB in both methods -----------
--- enjoy ---------
--- Hank Freeman : [email protected]
-----------------------------------
--- Scritp to get rowcounts and Memory size of index and Primary Keys
SELECT
FileGroupName = DS.name
,FileGroupType =
CASE DS.[type]
WHEN 'FG' THEN 'Filegroup'
WHEN 'FD' THEN 'Filestream'
WHEN 'FX' THEN 'Memory-optimized'
WHEN 'PS' THEN 'Partition Scheme'
ELSE 'Unknown'
END
,SchemaName = SCH.name
,TableName = OBJ.name
,IndexType =
CASE IDX.[type]
WHEN 0 THEN 'Heap'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'Nonclustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
WHEN 5 THEN 'Clustered columnstore'
WHEN 6 THEN 'Nonclustered columnstore'
WHEN 7 THEN 'Nonclustered hash'
END
,IndexName = IDX.name
,RowCounts = replace(convert(varchar,Cast(p.rows as money),1), '.00','') --- MUST show for all types when no Primary key
--,( Case WHEN IDX.[type] IN (2,6,7) then 0 else p.rows end )as Rowcounts_T
,AllocationDesc = AU.type_desc
/*
,RowCounts = p.rows --- MUST show for all types when no Primary key
,TotalSizeKB2 = Cast(Round(SUM(AU.total_pages / 0.128),0)as int) -- 128 pages per megabyte
,UsedSizeKB = Cast(Round(SUM(AU.used_pages / 0.128),0)as int)
,DataSizeKB = Cast(Round(SUM(AU.data_pages / 0.128),0)as int)
--replace(convert(varchar,cast(1234567 as money),1), '.00','')
*/
,TotalSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.total_pages / 0.128),0)as money),1), '.00','') -- 128 pages per megabyte
,UsedSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.used_pages / 0.128),0)as money),1), '.00','')
,DataSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.data_pages / 0.128),0)as money),1), '.00','')
,DataSizeKB2 = format(Round(SUM(AU.data_pages / 0.128),0),'##,##0')
,DataSizeKB3 = format(SUM(AU.data_pages / 0.128),'##,##0')
--SELECT Format(1234567.8, '##,##0.00')
---
,is_default = CONVERT(INT,DS.is_default)
,is_read_only = CONVERT(INT,DS.is_read_only)
FROM
sys.filegroups DS -- you could also use sys.data_spaces
LEFT JOIN sys.allocation_units AU ON DS.data_space_id = AU.data_space_id
LEFT JOIN sys.partitions PA
ON (AU.[type] IN (1,3) AND
AU.container_id = PA.hobt_id) OR
(AU.[type] = 2 AND
AU.container_id = PA.[partition_id])
LEFT JOIN sys.objects OBJ ON PA.[object_id] = OBJ.[object_id]
LEFT JOIN sys.schemas SCH ON OBJ.[schema_id] = SCH.[schema_id]
LEFT JOIN sys.indexes IDX
ON PA.[object_id] = IDX.[object_id] AND
PA.index_id = IDX.index_id
-----
INNER JOIN
sys.partitions p ON obj.object_id = p.OBJECT_ID AND IDX.index_id = p.index_id
WHERE
OBJ.type_desc = 'USER_TABLE' -- only include user tables
OR
DS.[type] = 'FD' -- or the filestream filegroup
GROUP BY
DS.name ,SCH.name ,OBJ.name ,IDX.[type] ,IDX.name ,DS.[type] ,DS.is_default ,DS.is_read_only -- discard different allocation units
,p.rows ,AU.type_desc ---
ORDER BY
DS.name ,SCH.name ,OBJ.name ,IDX.name
---
;
回答by Tom
remove the commas with a replace and convert:
用替换和转换删除逗号：
CONVERT(INT,REPLACE([varName],',',''))
where varName is the name of the variable that has numeric values in it with commas
其中 varName 是其中包含带逗号的数值的变量的名称
