Remove trailing zeros from decimal in SQL Server

Question

I have a DECIMAL(9,6) column DECIMAL(9,6) , i.e. it supports values ​​like 999,123456.

But when I insert data, for example 123.4567, it becomes 123.456700

How to remove these zeros?

Answer 1

If you do not want to convert it to a string, you cannot. You need to do this at the presentation level.

Answer 2

A decimal(9,6) stores 6 digits on the right side of the comma. Mapping trailing zeros or not is a formatting solution typically implemented on the client side.

But since SSMS formats are float without trailing zeros, you can remove trailing zeros by casting decimal to 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, but SSMS formats are decimal with a period. Apparently, a known issue .)

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): do not use g17 or more (or g8 or more with REAL), because the limited precision of the machine presentation 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' 

Also, note that according to the documentation :

FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR). This feature will not be removed because it depends on the presence of the CLR. Removing a function that requires the CLR will result in an error on the remote server.

Works also in SqlAzure.

Answer 4

 SELECT CONVERT(DOUBLE PRECISION, [ColumnName]) 

Answer 5

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

prints:

 2.55 

Answer 6

 Cast(20.5500 as Decimal(6,2)) 

must do it.

Answer 7

I had a similar problem, but also needed to remove a decimal point that did not have a decimal, here is my solution, which breaks the decimal into its components and based on the number of characters it takes from the decimal point in the string, the length of the fraction component (without using CASE). To make it even more interesting, my number was stored as a float without 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 a lot of help from the answers above.

Answer 8

I needed to remove trailing zeros on decimal points so that I could output a string of a certain length only with leading zeros

(for example, I needed to output 14 characters so that 142.023400 became 000000142.0234),

I used parsename , reverse and cast as int to remove 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 combine this to the beginning of the above)

Hope this helps someone.

Answer 9

can remove leading and trailing zeros in TSQL

• Convert it to a string using the STR TSQL function, if not a string, Then

• Remove leading and trailing zeros

   SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount 

• Additional information about the forum .

Answer 10

Try the following:

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

Gives 20.55

Answer 11

The best way is NOT to convert to FLOAT or MONEY before conversion due to the likelihood of loss of accuracy. Thus, safe methods may 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 12

Another option ...

I don't know how effective this is, but it seems to work and does not go through float:

 select replace(rtrim(replace( replace(rtrim(replace(cast(@value as varchar(40)), '0', ' ')), ' ', '0') , '.', ' ')), ' ', '.') 

The middle line removes trailing spaces, the outer two removes the period if there are no decimal digits

Answer 13

How about this? Assuming the data goes 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 14

 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 15

I understand this is an old post, but would like to provide the 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 16

I know that this thread is very old, but for those who do not use SQL Server 2012 or higher, or cannot use the FORMAT function for any reason, then the following works.

In addition, many solutions did not work if the number was less than 1 (for example, 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 17

try it.

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

Answer 18

The easiest way is to cast the value as FLOAT and then a string data type.

 CAST(CAST(123.456000 AS FLOAT) AS VARCHAR(100)) 

Answer 19

I had a similar problem needed to trim xx0000,x00000,xxx000 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 20

The DECIMAL column (9.6) is converted to a float without losing accuracy, so CAST (... AS float) will do the trick.

---

@HLGEM: saying that float is a bad choice for storing numbers, and "Never use float" is wrong - you just need to know your numbers, for example. temperature measurements will look good as floating.

@abatishchev and @japongskie: prefixes before stored in SQL processes and functions are still a good idea if it is not required; the links you mentioned instruct you not to use the "sp_" prefix for stored procedures that you should not use, other prefixes are exact, for example. "usp_" or "spBob _"

Link: "All integers with 6 or less 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 21

Try the following:

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

Answer 22

Try the following:

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