在SQL Server中删除尾随0

I have a column DECIMAL(9,6) i.e. it supports values like 999,123456.

我有一个十进制的列(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?

如何去除这些0 ?

22 个解决方案

#1

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.

十进制(9,6)在逗号的右边存储6位数字。是否显示尾零是一种格式化决策，通常在客户端实现。

But since SSMS formats float without trailing zeros, you can remove trailing zeroes by casting the decimal to a float:

但是由于SSMS格式是浮动的，没有拖尾零，您可以通过将小数转换为浮点数来删除拖尾零:

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.)

(我的十进制分隔符是一个逗号，但ssm格式是十进制的，带有一个点。显然一个已知的问题。)

#2

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 g17 or larger (or g8 or larger with REAL), because the limited precision of the machine representation causes unwanted effects:

使用浮点数(或实数)时要小心:不要使用g17或更大的(或g8或更大的实数)，因为机器表示的有限精度会造成不必要的影响:

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.

格式依赖于。net框架公共语言运行时(CLR)的存在。这个函数不会被移除，因为它取决于CLR的存在。远程调用需要CLR的函数会在远程服务器上导致错误。

Works in SqlAzure, too.

在SqlAzure工作。

#3

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

#4

SELECT REVERSE(ROUND(REVERSE(2.5500),1))

prints:

打印:

2.55

#5

Cast(20.5500 as Decimal(6,2))

should do it.

应该这样做。

#6

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.

我也有过类似问题,但还需要删除没有小数的小数点在场,这是我的解决方案将小数点分为其组件,和基地的字符数的小数点部分组件的字符串的长度(不使用)。更有趣的是，我的数字被存储为一个没有小数的浮点数。

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.

结果是痛苦的，我知道，但是在上面的答案的帮助下，我做到了。

#7

The best way is NOT converting to FLOAT or MONEY before 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 any decimal(x,y)

其中@value可以是任何小数(x,y)

#8

I needed to remove trailing zeros on my decimals so I could output a string of a certain length with only leading zeros

我需要删除小数点后的0，这样我就可以输出一个只有前导0的字符串

(e.g. I needed to output 14 characters so that 142.023400 would become 000000142.0234),

(例如，我需要输出14个字符，使142.023400变成000000142.0234)，

I used parsename, reverse and cast as int to remove the trailing zeros:

我使用了parsename、reverse和cast as int来删除后面的0:

SELECT
    PARSENAME(2.5500,2)
    + '.'
    + REVERSE(CAST(REVERSE(PARSENAME(2.5500,1)) as int))

(To then get my leading zeros I could replicate the correct number of zeros based on the length of the above and concatenate this to the front of the above)

(为了得到前导0，我可以根据上面的长度复制正确的0的数量，并将其连接到上面的前面)

I hope this helps somebody.

我希望这对某人有帮助。

#9

it is possible to remove leading and trailing zeros in TSQL

可以在TSQL中删除前导和尾随零。

1. Convert it to string using STR TSQL function if not string, Then

   如果不是字符串，那么使用STR TSQL函数将其转换为字符串

2. Remove both leading & trailing zeros

   删除前导和尾随零。

   SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount
   

3. More info on forum.

   更多信息在论坛。

#10

Try this :

试试这个:

SELECT REPLACE(TRIM(REPLACE(20.5500, "0", " ")), " ", "0")

Gives 20.55

给20.55

#11

I was reluctant to cast to float because of the potential for more digits to be in my decimal than float can represent

我不愿意对浮点数进行强制转换，因为在我的十进制数中，可能有比float所表示的更多的数字

FORMAT when 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

在使用标准的。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 this case there is no other layer; the user is literally running a stored procedure and putting the result in an email :/ )

当然，这是让数据层进行格式化的令人沮丧的实践(但在这种情况下，没有其他层;用户正在运行一个存储过程，并将结果放入电子邮件:/)

#12

Another option...

另一种选择……

I don't know how efficient this is but it seems to work and does not go via float:

我不知道这有多有效，但它似乎起作用了，并不是通过浮动来实现的:

select replace(rtrim(replace(
       replace(rtrim(replace(cast(@value as varchar(40)), '0', ' ')), ' ', '0')
       , '.', ' ')), ' ', '.')

The middle line strips off trailing spaces, the outer two remove the point if there are no decimal digits

中间的线去掉尾部的空格，如果没有十进制的数字，外面的两行去掉这个点

#13

How about this? Assuming data coming into your function as @thisData:

这个怎么样?假设数据以@thisData进入您的函数:

BEGIN
  DECLARE @thisText VARCHAR(255)
  SET @thisText = REPLACE(RTRIM(REPLACE(@thisData, '0', ' ')), ' ', '0')
  IF SUBSTRING(@thisText, LEN(@thisText), 1) = '.'
    RETURN STUFF(@thisText, LEN(@thisText), 1, '')
  RETURN @thisText
END

#14

case when left(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0'), 1) = '.'
then '0' 
else ''
end +

replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0') +

case when right(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0'), 1) = '.'
then '0' 
else ''
end

#15

I understand this is an old post but would like to provide SQL that i came up with

我知道这是一篇老文章，但我想提供我想到的SQL

DECLARE @value DECIMAL(23,3)
set @value = 1.2000
select @value original_val, 
    SUBSTRING(  CAST( @value as VARCHAR(100)), 
                0,
                PATINDEX('%.%',CAST(@value as VARCHAR(100)))
            )
      + CASE WHEN ROUND( 
                        REVERSE( SUBSTRING( CAST(@value as VARCHAR(100)),
                                        PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
                                        LEN(CAST(@value as VARCHAR(100)))
                                        )
                                )
                    ,1) > 0 THEN 
            '.' 
            +  REVERSE(ROUND(REVERSE(SUBSTRING( CAST(@value as VARCHAR(100)),
                                                PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
                                                LEN(CAST(@value as VARCHAR(100)))
                                                )
                ),1))
        ELSE '' END  AS modified_val

#16

I know this thread is very old but for those not using SQL Server 2012 or above or cannot use the FORMAT function for any reason then the following works.

我知道这个线程很老了，但是对于那些没有使用SQL Server 2012或以上的线程，或者由于任何原因不能使用FORMAT函数的线程，以下是可行的。

Also, a lot of the solutions did not work if the number was less than 1 (e.g. 0.01230000).

而且，如果这个数小于1(例如0.0123万)，很多解都不起作用。

Please note that the following does not work with negative numbers.

请注意，以下内容不适用于负数。

DECLARE @num decimal(28,14) = 10.012345000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0') 

set @num = 0.0123450000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0') 

Returns 10.012345 and 0.012345 respectively.

返回10.012345和0.012345。

#17

try this.

试试这个。

select CAST(123.456700 as float),cast(cast(123.4567 as DECIMAL(9,6)) as float)

#18

The easiest way is to CAST the value as FLOAT and then to a string data type.

最简单的方法是将值转换为浮点数，然后转换为字符串数据类型。

CAST(CAST(123.456000 AS FLOAT) AS VARCHAR(100))

#19

I had a similar problem, needed to trim trailing zeros from numbers like xx0000,x00000,xxx000

我也遇到过类似的问题，需要从xx0000、x00000、xxx000之类的数字中去掉末尾的0

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.

代码是字段的名称和要修剪的数字。希望这能帮助别人。

#20

A DECIMAL(9,6) column will convert to float without loss of precision, so CAST(... AS float) will do the trick.

十进制(9,6)列将转换为浮点数而不损失精度，因此CAST(…)作为浮动)将做的诀窍。

---

@HLGEM: saying that float is a poor choice for storing numbers and "Never use float" is not correct - you just have to know your numbers, e.g. temperature measurements would go nicely as floats.

@HLGEM:说浮点数不适合存储数字，“从不使用浮点数”是不正确的——你只需要知道你的数字，例如，温度测量会像浮点数一样顺利。

@abatishchev and @japongskie: prefixes in front of SQL stored procs and functions are still a good idea, if not required; the links you mentioned only instructs not to use the "sp_" prefix for stored procedures which you shouldn't use, other prefixes are fine e.g. "usp_" or "spBob_"

@abatishchev和@japongskie: SQL存储的proc和函数前面的前缀仍然是一个好主意，如果不需要的话;您提到的链接只指示不要对不应该使用的存储过程使用“sp_”前缀，其他前缀也可以。“usp_”或“spBob_”

Reference: "All integers with 6 or fewer significant decimal digits can be converted to an IEEE 754 floating-point value without loss of precision": https://en.wikipedia.org/wiki/Single-precision_floating-point_format

引用:“所有小于或等于6位有效小数的整数都可以转换为IEEE 754浮点值而不会丢失精度”:https://en.wikipedia.org/wiki/Single-precision_floating-point_format

#21

Try this:

试试这个:

select Cast( Cast( (ROUND( 35.457514 , 2) *100) as Int) as float ) /100

#22

Try this:

试试这个:

select isnull(cast(floor(replace(rtrim(ltrim('999,999.0000')),',','')) as int),0)