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

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

Answer 3

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.

Answer 4

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

Answer 5

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

prints:

2.55

Answer 6

Try this :

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

Gives 20.55

Answer 7

Cast(20.5500 as Decimal(6,2))

should do it.

Answer 8

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.

Answer 9

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)

Answer 10

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.

Answer 11

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

Answer 12

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.

Answer 13

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.

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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.

Answer 19

try this.

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

Answer 20

When I converted long deicmals to nvarchar, I used convertion to double precision and casting to nvarchar

SELECT CAST(CONVERT(DOUBLE PRECISION, 1.234567000000) as nvarchar(255))

Output:

1.23457

Answer 21

Try this:

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

Answer 22

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

Answer 23

Try this:

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