Remove Trailing decimal zero

Question

I have these values

ROWA
-------
0.23
0.2350
0.1000
1.250
1200
1.300

I wanted to convert to a format where only decimal trailing zero are remove I tried using this

select cast(RowA as decimal(18,3)) from dual.

But the result remove the leading zero of the decimal.

ROWA
-------
.23
.235
.1
1.25
1200
1.3

How do I convert to

ROWA
-------
0.23
0.235
0.1
1.25
1200
1.3

See http://stackoverflow.com/questions/6695604/oracle-why-does-the-leading-zero-of-a-number-disappear-when-converting-it-to-c — Vadzim, Jan 14 '15 at 09:59

score 5 · Accepted Answer · answered Sep 17 '13 at 02:07

Your samples show two different formats - one for decimal numbers, and a different one for integers.

with sampledata as
  (select 0.23 as rowa from dual
union all select 0.2350 from dual
union all select 0.1000 from dual
union all select 1.250 from dual
union all select 1200 from dual
union all select 1.300 from dual
 )
select TO_CHAR(rowa
              ,CASE WHEN rowa = TRUNC(rowa)
               then 'fm999999999999999990'
               else 'fm999999999999999990D999'
               end) as rowa
from sampledata;

ROWA
=====
0.23
0.235
0.1
1.25
1200
1.3

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

Here is the most concise solution I'm currenly aware of:

rtrim(to_char(rowa, 'FM999999999999999990.999'), '.')

Taken from my another detailed answer.

edited May 23 '17 at 12:25

Community

1
1

answered Jan 14 '15 at 15:44

Vadzim

24,954
11
143
151

score 0 · Answer 3 · answered Mar 27 '17 at 09:59

0

or how about

rtrim ( rtrim ( to_char ( rowa ), 0 ), '.' )

answered Mar 27 '17 at 09:59

IanMc

41
4

Remove Trailing decimal zero

3 Answers3

Linked