[sql] 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?

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.

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.

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.

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

prints:

2.55

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

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

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

Try this:

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

Out of all the above answers, I found only two that actually worked. do not convert to float, proof here that 3.175 does not work if you do that.

SELECT  round(cast(3.175 as float), 2) as roundingcheck,    
CASE WHEN
    round(cast(3.175 as float), 2) = 3.18 THEN 'PASSED' ELSE 'FAILED' END
    as roundecheck,     
    CAST(round(TRY_CONVERT(DECIMAL(28,2), cast(3.175 as
    float)), 2) as nvarchar(max)) as roundconvert,  
    round(CAST(3.175 as DECIMAL(18,10)), 4) as check1,  
cast(CAST(round(CAST(3.175 as DECIMAL(18,10)), 4) as decimal(18,2)) as nvarchar(max))  as
    roundconvert2,  
cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,2)) as nvarchar(max))  as roundconvert3,
cast(CAST(CAST(3.149 as DECIMAL(18,10)) as decimal(18,1)) as nvarchar(max))  as roundconvert4,  
cast(FORMAT(round(CAST(3.175 as
    DECIMAL(18,10)), 2), '0.######') as nvarchar(max))  as roundconvert5

Result:

3.17    FAILED  3.17    3.1750000000    3.18    3.18    3.1    3.18

Working answers:

• If you want to round to 2 decimal places at the same time, use this:

    cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,2)) as nvarchar(max))  as roundconvert3,
  

• If you don't know how many decimals there are going to be you can use this and use it with round if you need:

   cast(FORMAT(round(CAST(3.175 as DECIMAL(18,10)), 2), '0.######') as nvarchar(max))
  

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.

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)

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

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

Cast(20.5500 as Decimal(6,2))

should do it.

Try this:

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

try this.

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

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

Try this :

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

Gives 20.55

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

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.

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.

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