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?

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

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

Solution 3

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

Solution 4

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

Solution 5

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

prints:

2.55

View more solutions