有没有什么简单的方法可以在 T-SQL 中格式化小数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2150507/
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
Is there any simple way to format decimals in T-SQL?
提问by Ian Henry
I know it could be done trivially in a non-SQL environment [post-data processing, frontend, what have you], but that's not possible at the moment. Is there a way to take a decimal(5,2)and convert it to a varcharwithout the trailing zeroes/decimal points? For example:
我知道这可以在非 SQL 环境中轻松完成 [后数据处理、前端，你有什么]，但目前这是不可能的。有没有办法在没有尾随零/小数点decimal(5,2)的varchar情况下将a转换为 a ？例如：
declare @number decimal(5,2)
set @number = 123.00
select cast(@number as varchar) as FormattedNumber
And the result is '123.00'. Is there a (simple) way to get '123' instead? And likewise, instead of '123.30', '123.3'? Could do it by figuring out whether or not the hundredths/tenths places were 0 and manually trimming characters, but I wanted to know if there was a more elegant solution.
结果是“123.00”。有没有（简单的）方法来获得“123”？同样，不是'123.30'，而是'123.3'？可以通过确定百分之几/十分之一位置是否为 0 并手动修剪字符来做到这一点，但我想知道是否有更优雅的解决方案。
回答by Frank Kalis
What about:
关于什么：
SELECT CAST(CAST(@number AS float) AS varchar(10))
However you may want to test this carefully with your raw data first.
但是，您可能希望首先使用原始数据仔细测试。
回答by user250118
This way is pretty simple:
这种方法非常简单：
DECLARE @Number DECIMAL(5,2)
SELECT @Number = 123.65
SELECT FormattedNumber = CAST(CAST(@Number AS DECIMAL(3,0)) AS VARCHAR(4))
Returns '124'.
返回“124”。
The only thing to consider is whether you want to round up/down, or just strip the zeroes and decimal points without rounding; you'd cast the DECIMAL as an INT in the second case.
唯一需要考虑的是您是要向上/向下舍入，还是只去掉零和小数点而不舍入；在第二种情况下，您会将 DECIMAL 转换为 INT。
回答by Mark Micallef
For controlled formatting of numbers in T-SQL you should use the FORMAT()function. For example:
对于 T-SQL 中数字的受控格式，您应该使用该FORMAT()函数。例如：
DECLARE @number DECIMAL(9,2); SET @number = 1234567.12;
DECLARE @formatted VARCHAR(MAX); SET @formatted = FORMAT(@number, 'N0', 'en-AU');
PRINT @formatted;
The result will be:
结果将是：
1,234,567
The arguments to the FORMAT()function are:
该FORMAT()函数的参数是：
FORMAT(value, format [, culture])
The value argument is your number. The format argument is a CLR type formatting string (in this example, I specified "normal number, zero precision"). The optional culture argument allows you to override the server culture setting to format the number as per a desired culture.
value 参数是您的号码。format 参数是一个 CLR 类型的格式化字符串（在这个例子中，我指定了“正常数字，零精度”）。可选的文化参数允许您覆盖服务器文化设置以根据所需的文化设置数字格式。
See also the MSDN ref page for FORMAT().
另请参阅MSDN 参考页面FORMAT()。
回答by Christopher Rose
If you have SQL Server 2012 or Greater you can use the format function like this:
如果您有 SQL Server 2012 或更高版本，则可以使用格式函数，如下所示：
select format(@number,'0') as FormattedNumber
Of course the format function will return an nvarchar, and not a varchar. You can cast to get a specific type.
当然，格式函数将返回一个 nvarchar，而不是一个 varchar。您可以强制转换以获取特定类型。
回答by Andrew
Use the Format(value,format string,culture) function in SQL Server 2012+
在 SQL Server 2012+ 中使用 Format(value,format string,culture) 函数
回答by Randy Minder
The Convert function may do what you want to do.
Convert 函数可能会执行您想要执行的操作。
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm
回答by roufamatic
Let me try this again....
让我再试试这个......
CREATE FUNCTION saneDecimal(@input decimal(5,2)) returns varchar(10)
AS
BEGIN
DECLARE @output varchar(10)
SET @output = CAST(@input AS varchar(10))
DECLARE @trimmable table (trimval char(1))
INSERT @trimmable VALUES ('0')
INSERT @trimmable VALUES ('.')
WHILE EXISTS (SELECT * FROM @trimmable WHERE trimval = CAST(SUBSTRING(@output, LEN(@output), 1) AS char(1)))
SET @output = LEFT(@output, LEN(@output) - 1)
RETURN @output
END
GO
SELECT dbo.saneDecimal(1.00)
回答by Andomar
You could strip the trailing zeroes in a while loop:
您可以在 while 循环中去除尾随零：
declare @number decimal(5,2)
declare @str varchar(100)
set @number = 123.00
set @str = @number
while substring(@str,len(@str),1) in ('0','.',',')
set @str = substring(@str,1,len(@str)-1)
But as AdaTheDev commented, this is more easily done client-side.
但正如 AdaTheDev 评论的那样，这在客户端更容易完成。
回答by Mark Brackett
Simple and elegant? Not so much...but that's T-SQL for you:
简单而优雅？不是很多......但这是你的T-SQL：
DECLARE @number decimal(5,2) = 123.00
DECLARE @formatted varchar(5) = CAST(@number as varchar)
SELECT
LEFT(
@formatted,
LEN(@formatted)
- PATINDEX('%[^0.]%', REVERSE(@formatted))
+ 1
)
回答by user1664143
Also, take a look at the T-SQL STR function in Books Online; this can be used for formatting floats and might work for your case. For some reason it doesn't come up in Google searches relating to this problem.
另外，看看联机丛书中的 T-SQL STR 函数；这可用于格式化浮点数，可能适用于您的情况。出于某种原因，它没有出现在与此问题相关的 Google 搜索中。
