Remove Trailing Zeros from Decimal in SQL Server

Remove trailing zeros from decimal in SQL Server

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

How to remove trailing zeros from decimal number in SQL Server?

i think you should use

select cast(CAST(25.00 as decimal(18,5)) as float)

it return 25 but you cast

   select cast(CAST(25.23 as decimal(18,5)) as float)

it return 25.23

Remove the trailing zeros after Decimal points without truncating/ approximating the value in SQL server

If 2012+ The #'s indicate an optional display

Select format(0.0375000,'0.######')  Returns 0.0375

Select format(0.037502,'0.######')  Returns 0.037502

Sorry didn't see stored as varchar()

Select format(cast(somecolumn as decimal(18,8)),'0.######')

sql server remove trailing zeros in decimal

remove-trailing-zeros-from-decimal-in-sql-server

Remove some of trailing zeros from decimal

You can use the str() function with trim():

select trim(str(value, 20, picture))

str() converts a number to a string with the specified length and precision. Sadly, it produces a fixed length string, left padded with spaces. The trim() removes the spaces.

SQL Server : remove trailing 0's after decimal point. nvarchar datatype

DECLARE @x as varchar(32) = '653710000.01';

--note rounding error occurs
select CAST(CAST(@x As FLOAT) AS NVARCHAR(32));

--how to break this one?
select 
        CASE WHEN PATINDEX('%[.]%',REVERSE(@x)) = PATINDEX('%[123456789.]%',REVERSE(@x)) 
            THEN 
                   LEFT(@x, LEN(@X) - PATINDEX('%[.]%',REVERSE(@x))) 
            ELSE  

                CASE WHEN PATINDEX('%[.]%',REVERSE(@x)) > 0 
                    THEN 
                        LEFT(@x, 1 + LEN(@X) - PATINDEX('%[123456789.]%',REVERSE(@x)))  
                    ELSE
                        @X
                    END
            END  ;

Remove trailing zeroes using sql

You just have to cast it as a decimal(12,2).

select cast(round(AVG(CAST(k.TotalNumberDays AS numeric(12,2))),2) as decimal(12,2)) TotalNumber

---

Related Topics