Remove trailing zero from decimal number

Question

I have a one database table field called Amount which type is decimal(18,6). so it is stored in database up to 6 decimal points like 9.786534 But while retrieving that field using select query i have to take care like following

1. Remove trialling zero e.g if number is 9.230000 then result is only 9.23

2. If decimal points are all zero then only remove only four trialling zero e.g If number is 9.000000 then result is 9.00

3. Result is up to 2 decimal point if there are trialling zero.

If we write simple query like

select TOP 1 Amount From EmployeeMaster

then it gives 9.230000

but my intension is to remove trailing zero..

Please help me..

Answer 1

It works for removing trailing zeros, but I am still not able to convert 9 to 9.00 in this method.

Declare @myvalue varchar(50), 
        @Price Varchar(50) 

Set @Price = '9.230000' 

set @Myvalue = reverse(substring(@Price,patindex('%.%',@Price)+1,len(@Price))) 


SELECT 
case  
When  patindex('%.%[1-9]%',@price) = 0 Then  
    substring(@price,1,patindex('%.%',@price)-1) 
else 
    substring(@price,1,patindex('%.%',@price)-1) + '.' +  Reverse(substring(@Myvalue,patindex('%[1-9]%',@Myvalue),len(@Myvalue))) 
END 

Answer 2

Coming from decimal(18,6) you could do...

select cast(Amount as decimal(18,2))

Most databases that support the CAST function will round the number while converting it. On SQLServer this is what I would do if I wanted rounding.

If what you actually want is a string with only two digits after the decimal then you could

select cast((Amount as decimal(18,2)) as nvarchar)

nvarchar is SQLServer's variable length unicode type. Databases do not agree much on string types. Your database may have a different one. The rest of that sql is ANSI standard. Not all dbs support that either but many do.

Answer 3

This should work

SELECT CAST(REPLACE(RTRIM(REPLACE(CAST(CAST(33.9082976 AS DECIMAL(38,8)) AS NVARCHAR(256)),'0',' ')),' ','0') AS FLOAT)

Answer 4

Does this work?

select TOP 1 ROUND(Amount, 2) From EmployeeMaster 

Answer 5

TRY below mentioned code.

SELECT TOP 1 CONVERT(DECIMAL(10,2),Amount) From EmployeeMaster 

Hope it will work as expected.

Answer 6

An alternative approach:

1) convert the decimal to a string;

2) split the string into 2 parts, separating the last 4 characters from the rest of the string;

3) remove trailing zeros from the last 4 characters;

4) concatenate the two parts back.

WITH data (V)     AS (SELECT CAST(9.786534 AS decimal(18,6))
                      UNION ALL
                      SELECT CAST(9.78653  AS decimal(18,6))
                      UNION ALL
                      SELECT CAST(9.7800   AS decimal(18,6))
                      UNION ALL
                      SELECT CAST(9.7      AS decimal(18,6))
                      UNION ALL
                      SELECT CAST(9.00000  AS decimal(18,6))
                     )

,    AsString (V) AS (SELECT CAST(V AS varchar) FROM data)

,    Split (L, R) AS (SELECT LEFT(V, LEN(V) - 4), RIGHT(V, 4) FROM AsString)

,    Adjusted     AS (SELECT L,
                             REPLACE(RTRIM(REPLACE(R, '0', ' ')), ' ', '0') AS R
                      FROM Split)

SELECT Result = L + R FROM Adjusted

The output of the above script is:

Result
--------
9.786534
9.78653
9.78
9.70
9.00

Answer 7

I guess using patindex in your case:

CASE WHEN FLOOR(Amount) <> CEILING(Amount) THEN 
     LTRIM(SUBSTRING(STR(Amount, 18, 6), 1, LEN(STR(Amount, 18, 6)) - PATINDEX('%[^0]%', REVERSE(str(Amount, 18, 6))) + 1))
     ELSE STR(Amount,18,2)
END

Answer 8

for a decimal(18,6) field this should work:

select trim(to_char(Amount, '999999999999999999.99')) from EmployeeMaster 

(at least for Oracle, not sure about other types)