在 SQL Server 2008 R2 中用逗号格式化数字但没有小数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13207075/
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
Format a number with commas but without decimals in SQL Server 2008 R2?
提问by Sunil
I am using the following to create a comma formatted number in T-SQL. How can I get rid of the decimal point and the digits after decimal. So if I get 1,112.00 after formatting, how would I get only 1,112?
我正在使用以下内容在 T-SQL 中创建一个逗号格式的数字。我怎样才能摆脱小数点和小数点后的数字。所以如果我在格式化后得到 1,112.00，我怎么会只得到 1,112？
SELECT CONVERT(varchar, CAST(1112 AS money), 1)
回答by Mitch Wheat
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT left(@val, len(@val) - 3)
This also works with digits after the decimal point:
这也适用于小数点后的数字：
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT left(@val, len(@val) - 3)
Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.
注意：正如@Mahmoud Gamal 指出的那样，格式化通常更适合在前端执行。
回答by Mahmoud Gamal
Like so:
像这样：
SELECT REPLACE(CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1), '.00', '');
This will always work fine. Since CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1)will always return a number with .00. However, the MitchWheat's answeris better in case there is a number with decimal numbers after the comma.
这将始终正常工作。由于CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1)将始终返回一个带有.00. 但是，如果逗号后面有一个带十进制数字的数字，则MitchWheat 的答案会更好。
Note that: You should consider to do this formatting stuff in the front end application. T-SQL is not about formatting.
请注意：您应该考虑在前端应用程序中执行此格式化操作。T-SQL 与格式化无关。
回答by Govind
PARSENAME(CONVERT(VARCHAR,CAST(1112 AS MONEY),1),2)
It will work nicely.
它会很好地工作。
When convert number to money datatype automatically 2 zeros will be added after decimal. PARSENAME function will remove that zeros.
自动将数字转换为货币数据类型时，会在小数点后添加 2 个零。PARSENAME 函数将删除该零。
回答by Jorge Alejandro Ramirez Lopez
https://database.guide/how-to-format-numbers-in-sql-server/
https://database.guide/how-to-format-numbers-in-sql-server/
Starting on SQL 2012 you can write:
从 SQL 2012 开始，您可以编写：
SELECT FORMAT(ColumnName, 'N0');
回答by Sunil
After some research, I found 2 possible answers to my initial question. They are listed below.
经过一番研究，我找到了最初问题的 2 个可能答案。它们在下面列出。
Option 1: The answer from Mitch Wheat is a possible answer. However, when one wants to format a column value within a SELECT, then we would have to create a user-defined scalar function using Mitch's T-SQL code, and call this UDF from our SQL.
选项 1：Mitch Wheat 的答案是一个可能的答案。但是，当想要在 SELECT 中格式化列值时，我们必须使用 Mitch 的 T-SQL 代码创建一个用户定义的标量函数，并从我们的 SQL 中调用此 UDF。
-- =============================================
-- Description: Formats a number and truncates
-- the decimal part. You can pass
-- a number as a string or a numeric type.
-- =============================================
CREATE FUNCTION dbo.Utility_fn_FormatNumberAndTruncateDecimals
(
@unFormattedNumber VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @val VARCHAR(100)
SET @val = convert(VARCHAR(50), cast(@unFormattedNumber AS MONEY), 1)
RETURN (SELECT left(@val, len(@val) - 3))
END
GO
--call this function using either of the SELECTS below
SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals('233444')
SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals(233444.345)
Option 2:We can use an inbuilt system function called 'parsename' as in T-SQL code below, to format and truncate decimals.
选项 2：我们可以使用名为 'parsename' 的内置系统函数，如下面的 T-SQL 代码所示，来格式化和截断小数。
SELECT PARSENAME(CONVERT(VARCHAR, CAST('2334442221.345222' AS MONEY), 1),2)
