Remove trailing zeros from decimal in SQL Server

问题:

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

But when I insert data like 123,4567 it becomes 123,456700

How to remove those zeros?

回答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.

But since SSMS formats float without trailing zeros, you can remove trailing zeroes by casting the decimal to 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.)

回答2:

You can use the FORMAT() function (SqlAzure and 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:

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.

Works in SqlAzure, too.

回答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 was reluctant to cast to float because of the potential for more digits to be in my decimal than float can represent

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

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:

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#####

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 :/ )

回答7:

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.

回答8:

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 :

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)

回答9:

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

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

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

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)

I hope this helps somebody.

回答10:

it is possible to remove leading and trailing zeros in TSQL

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

2. Remove both leading & trailing zeros

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

3. More info on forum.

回答11:

Try this :

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

Gives 20.55

回答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:

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

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.

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

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.

回答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

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.

---

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

@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_"

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

回答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)