SQL Server 用逗号格式化小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13245663/
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
SQL Server format decimal places with commas
提问by Snippet
How can I convert the decimal values to have some commas?
如何将十进制值转换为有一些逗号？
thisHelps me. But my problem the decimal places are set to 2 only..I want the decimal to be 2, 3, or 4..example
这对我有帮助。但我的问题是小数位只设置为 2..我希望小数为 2、3 或 4..example
1,234.123 or 1,234.12345
I tried
我试过
convert(varchar, convert(decimal(18, 4), 1234.1234567), 1)
Output : 1234.1234
输出：1234.1234
There is no comma. But if I use money the decimal are 2 only
没有逗号。但如果我用钱，小数点只有 2
convert(varchar, convert(money, 1234.1234567), 1)
Output : 1,234.12
转换（varchar，转换（钱，1234.1234567），1）
输出：1,234.12
回答by RichardTheKiwi
Thankfully(?), in SQL Server 2012+, you can now use FORMAT()to achieve this:
值得庆幸的是（？），在SQL Server 2012+ 中，您现在可以使用FORMAT()来实现这一点：
FORMAT(@s,'#,0.0000')
在以前的版本中，冒着看起来很丑的风险
[Query]:
[查询]:
declare @s decimal(18,10);
set @s = 1234.1234567;
select replace(convert(varchar,cast(floor(@s) as money),1),'.00',
'.'+right(cast(@s * 10000 +10000.5 as int),4))
In the first part, we use MONEY->VARCHAR to produce the commas, but FLOOR() is used to ensure the decimals go to .00. This is easily identifiable and replaced with the 4 digits after the decimal place using a mixture of shifting (*10000) and CAST as INT (truncation) to derive the digits.
在第一部分中，我们使用 MONEY->VARCHAR 来生成逗号，但使用 FLOOR() 来确保小数转到.00. 这很容易识别并替换为小数点后的 4 位数字，使用移位 ( *10000) 和 CAST 作为 INT（截断）的混合来导出数字。
[Results]:
[结果]:
| COLUMN_0 |
--------------
| 1,234.1235 |
But unless you have to deliver business reports using SQL Server Management Studio or SQLCMD, this is NEVERthe correct solution, even if it can be done. Any front-end or reporting environment has proper functions to handle display formatting.
但是，除非您必须使用 SQL Server Management Studio 或 SQLCMD 交付业务报告，否则即使可以完成，这也永远不是正确的解决方案。任何前端或报告环境都有适当的功能来处理显示格式。
回答by bummi
without considering this to be a good idea...
不认为这是一个好主意......
select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))
Function
功能
-- 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
回答by Darren Griffith
From a related SO question: Format a number with commas but without decimals in SQL Server 2008 R2?
来自相关的 SO 问题： 在 SQL Server 2008 R2 中用逗号格式化数字但没有小数？
SELECT CONVERT(varchar, CAST(1112 AS money), 1)
This was tested in SQL Server 2008 R2.
这已在 SQL Server 2008 R2 中进行了测试。
回答by Drew
If you are using SQL Azure Reporting Services, the "format" function is unsupported. This is really the only way to format a tooltip in a chart in SSRS. So the workaround is to return a column that has a string representation of the formatted number to use for the tooltip. So, I do agree that SQL is not the place for formatting. Except in cases like this where the tool does not have proper functions to handle display formatting.
如果您使用的是 SQL Azure Reporting Services，则不支持“格式”功能。这确实是在 SSRS 中格式化图表中工具提示的唯一方法。因此，解决方法是返回一个列，该列具有用于工具提示的格式化数字的字符串表示形式。所以，我同意 SQL 不是格式化的地方。除非在这种情况下工具没有适当的功能来处理显示格式。
In my case I needed to show a number formatted with commas and no decimals (type decimal 2) and ended up with this gem of a calculated column in my dataset query:
在我的例子中，我需要显示一个用逗号格式化的数字，没有小数（类型小数 2），最后在我的数据集查询中得到了一个计算列的宝石：
,Fmt_DDS=reverse(stuff(reverse(CONVERT(varchar(25),cast(SUM(kv.DeepDiveSavingsEst) as money),1)), 1, 3, ''))
,Fmt_DDS=reverse(stuff(reverse(CONVERT(varchar(25),cast(SUM(kv.DeepDiveSavingsEst) as money),1)), 1, 3, ''))
It works, but is very ugly and non-obvious to whoever maintains the report down the road. Yay Cloud!
它有效，但对于以后维护报告的人来说非常丑陋且不明显。耶云！
回答by alzaimar
SQL (or to be more precise, the RDBMS) is not meant to be the right choice for formatting the output. The database should deliver raw data which then should be formatted (or more general: processed) in the destination application.
SQL（或者更准确地说，RDBMS）并不是格式化输出的正确选择。数据库应提供原始数据，然后应在目标应用程序中对其进行格式化（或更一般地说：处理）。
However, depending on the specific system you use, you may write a UDF (user defined function) to achive what you want. But please bear in mind that you then are in fact returning a varchar, which you will not be able to further process (e.g. summarize).
但是，根据您使用的特定系统，您可以编写 UDF（用户定义函数）来实现您想要的功能。但请记住，您实际上是在返回一个 varchar，您将无法对其进行进一步处理（例如汇总）。
