Remove trailing zeros from decimal in SQL Server

SqlTsqlSql Server-2008FormattingDecimal

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

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

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

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

Solution 5 - Sql

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

prints:

2.55

Solution 6 - Sql

Try this :

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

Gives 20.55

Solution 7 - Sql

Cast(20.5500 as Decimal(6,2))

should do it.

Solution 8 - Sql

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.

Solution 9 - Sql

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)

Solution 10 - Sql

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.

Solution 11 - Sql

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

Solution 12 - Sql

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.

Solution 13 - Sql

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.

Solution 14 - Sql

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

Solution 15 - Sql

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

Solution 16 - Sql

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

Solution 17 - Sql

try this.

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

Solution 18 - Sql

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

Solution 19 - Sql

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

Solution 20 - Sql

Try this:

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

Solution 21 - Sql

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.

Solution 22 - Sql

Try this:

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

Solution 23 - Sql

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

Categories
Recommended Sql Solutions
Recommended Tsql Solutions
Recommended Sql Server-2008 Solutions
Recommended Formatting Solutions
Recommended Decimal Solutions

Previous Solution:

Stopwatch vs. using System.DateTime.Now for timing events

C#PerformanceDatetimeTimingStopwatch

Next Solution:

php: determine where function was called from

PhpFunction Calls

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionabatishchevView Question on Stackoverflow
Solution 1 - SqlAndomarView Answer on Stackoverflow
Solution 2 - Sqlrobert4View Answer on Stackoverflow
Solution 3 - SqlCaius JardView Answer on Stackoverflow
Solution 4 - SqlBat_ProgrammerView Answer on Stackoverflow
Solution 5 - Sqluser1959416View Answer on Stackoverflow
Solution 6 - SqlToddView Answer on Stackoverflow
Solution 7 - SqlPeter JonesView Answer on Stackoverflow
Solution 8 - SqlAdge CutlerView Answer on Stackoverflow
Solution 9 - SqlMahmoud MoravejView Answer on Stackoverflow
Solution 10 - SqlKazeem MuritalaView Answer on Stackoverflow
Solution 11 - SqlSQLianView Answer on Stackoverflow
Solution 12 - SqlAliView Answer on Stackoverflow
Solution 13 - SqlIshtiaqView Answer on Stackoverflow
Solution 14 - SqlMrNazgulView Answer on Stackoverflow
Solution 15 - SqlbeloblotskiyView Answer on Stackoverflow
Solution 16 - SqlAbhiView Answer on Stackoverflow
Solution 17 - SqlVishal KiriView Answer on Stackoverflow
Solution 18 - SqlBrad RaicheView Answer on Stackoverflow
Solution 19 - SqlhamishView Answer on Stackoverflow
Solution 20 - SqlVipin J SView Answer on Stackoverflow
Solution 21 - SqlAlan SchofieldView Answer on Stackoverflow
Solution 22 - SqlkierzoView Answer on Stackoverflow
Solution 23 - SqlKobusView Answer on Stackoverflow