Remove trailing zeros from decimal in SQL Server

Question

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?

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

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