从 SQL Server 中的十进制中删除尾随零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2938296/
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
Remove trailing zeros from decimal in SQL Server
提问by abatishchev
I have a column DECIMAL(9,6)i.e. it supports values like 999,123456.
我有一列，DECIMAL(9,6)即它支持 999,123456 之类的值。
But when I insert data like 123,4567 it becomes 123,456700
但是当我插入像 123,4567 这样的数据时，它变成了 123,456700
How to remove those zeros?
如何去除这些零？
回答by Andomar
A decimal(9,6)stores 6 digits on the right side of the comma. Whether to display trailing zeroes or not is a formatting decision, usually implemented on the client side.
Adecimal(9,6)在逗号的右侧存储 6 位数字。是否显示尾随零是一个格式决定，通常在客户端实现。
But since SSMS formats floatwithout trailing zeros, you can remove trailing zeroes by casting the decimalto a float:
但是由于 SSMS 格式float没有尾随零，您可以通过decimal将 a 强制转换为删除尾随零float：
select
cast(123.4567 as DECIMAL(9,6))
, cast(cast(123.4567 as DECIMAL(9,6)) as float)
prints:
印刷：
123.456700 123,4567
(My decimal separator is a comma, yet SSMS formats decimal with a dot. Apparently a known issue.)
（我的小数点分隔符是逗号，但 SSMS 使用点格式化十进制数。显然是一个已知问题。）
回答by robert4
You can use the FORMAT()function (SqlAzure and Sql Server 2012+):
您可以使用该FORMAT()功能（SqlAzure 和 Sql Server 2012+）：
SELECT FORMAT(CAST(15.12 AS DECIMAL(9,6)), 'g18') -- '15.12'
SELECT FORMAT(CAST(0.0001575 AS DECIMAL(9,6)), 'g10') -- '0.000158'
SELECT FORMAT(CAST(2.0 AS DECIMAL(9,6)), 'g15') -- '2'
Be careful when using with FLOAT (or REAL): don't use g17or larger (or g8or larger with REAL), because the limited precision of the machine representation causes unwanted effects:
与 FLOAT（或 REAL）一起使用时要小心：不要使用g17或更大（g8或更大与 REAL），因为机器表示的有限精度会导致不良影响：
SELECT FORMAT(CAST(15.12 AS FLOAT), 'g17') -- '15.119999999999999'
SELECT FORMAT(CAST(0.9 AS REAL), 'g8') -- '0.89999998'
SELECT FORMAT(CAST(0.9 AS REAL), 'g7') -- '0.9'
Furthermore, note that, according to the documentation:
此外，请注意，根据文档：
FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.
FORMAT 依赖于 .NET Framework 公共语言运行时 (CLR) 的存在。此函数不会被远程处理，因为它取决于 CLR 的存在。远程处理需要 CLR 的函数会导致远程服务器出错。
Works in SqlAzure, too.
也适用于 SqlAzure。
回答by Bat_Programmer
SELECT CONVERT(DOUBLE PRECISION, [ColumnName])
回答by Caius Jard
I was reluctant to cast to float because of the potential for more digits to be in my decimal than float can represent
我不愿意转换为浮点数，因为小数点中的数字可能多于浮点数所能表示的数字
FORMATwhen used with a standard .net format string 'g8' returned the scientific notation in cases of very small decimals (eg 1e-08) which was also unsuitable
FORMAT当与标准 .net 格式字符串 'g8' 一起使用时，在非常小的小数（例如 1e-08）的情况下返回科学记数法，这也是不合适的
Using a custom format string (https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings) allowed me to achieve what I wanted:
使用自定义格式字符串（https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings）让我能够实现我想要的：
DECLARE @n DECIMAL(9,6) =1.23;
SELECT @n
--> 1.230000
SELECT FORMAT(@n, '0.######')
--> 1.23
If you want your number to have at least one trailing zero, so 2.0 does not become 2, use a format string like 0.0#####
如果您希望您的数字至少有一个尾随零，因此 2.0 不会变成 2，请使用类似的格式字符串 0.0#####
The decimal point is localised, so cultures that use a comma as decimal separator will encounter a comma output where the . is
小数点是本地化的，因此使用逗号作为小数点分隔符的文化将遇到逗号输出，其中 . 是
Of course, this is the discouragable practice of having the data layer doing formatting (but in my case there is no other layer; the user is literally running a stored procedure and putting the result in an email :/ )
当然，这是让数据层进行格式化的令人沮丧的做法（但在我的情况下没有其他层；用户实际上是在运行存储过程并将结果放入电子邮件中：/）
回答by user1959416
SELECT REVERSE(ROUND(REVERSE(2.5500),1))prints:
印刷：
2.55回答by Peter Jones
Cast(20.5500 as Decimal(6,2))
should do it.
应该这样做。
回答by Adge Cutler
I had a similar issue, but was also required to remove the decimal point where no decimal was present, here was my solution which splits the decimal into its components, and bases the number of characters it takes from the decimal point string on the length of the fraction component (without using CASE). To make matters even more interesting, my number was stored as a float without its decimals.
我有一个类似的问题，但也需要删除没有小数点的小数点，这是我的解决方案，它将小数点拆分为它的组成部分，并将小数点字符串中的字符数基于分数分量（不使用 CASE）。更有趣的是，我的数字被存储为没有小数的浮点数。
DECLARE @MyNum FLOAT
SET @MyNum = 700000
SELECT CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),2) AS VARCHAR(10))
+ SUBSTRING('.',1,LEN(REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0')))
+ REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0')
The result is painful, I know, but I got there, with much help from the answers above.
结果很痛苦，我知道，但我在上面的答案中得到了很大帮助。
回答by Mahmoud Moravej
The best way is NOT converting to FLOATor MONEYbefore converting because of chance of loss of precision. So the secure ways can be something like this :
最好的方法是在转换之前不要转换为FLOAT或MONEY，因为可能会损失精度。所以安全的方式可以是这样的：
CREATE FUNCTION [dbo].[fn_ConvertToString]
(
@value sql_variant
)
RETURNS varchar(max)
AS
BEGIN
declare @x varchar(max)
set @x= reverse(replace(ltrim(reverse(replace(convert(varchar(max) , @value),'0',' '))),' ',0))
--remove "unneeded "dot" if any
set @x = Replace(RTRIM(Replace(@x,'.',' ')),' ' ,'.')
return @x
END
where @value can be anydecimal(x,y)
其中@value 可以是任何十进制(x,y)
回答by Todd
Try this :
尝试这个 ：
SELECT REPLACE(TRIM(REPLACE(20.5500, "0", " ")), " ", "0")
Gives 20.55
给 20.55
回答by Kazeem Muritala
I had a similar problem, needed to trim trailing zeros from numbers like xx0000,x00000,xxx000
我有一个类似的问题，需要从数字中修剪尾随零，如 xx0000,x00000,xxx000
I used:
我用了：
select LEFT(code,LEN(code)+1 - PATINDEX('%[1-Z]%',REVERSE(code))) from Tablename
Code is the name of the field with the number to be trimmed. Hope this helps someone else.
代码是带有要修剪的数字的字段的名称。希望这对其他人有帮助。
