Resolution: Oracle does not store or display formatting (even on non-persisted values as shown below). A Formatter must be applied for anything other than that. Thanks to the folks that posted.

Question: Thought this would be trivial. If a decimal value is 0.40 I'd like to TO_CHAR(0.40) and get '0.40' back. Likewise, 50 should be to_char'd to '50'. As simple as what goes in, comes out, but as a CHAR, *in all cases*. So far I've tried the following:

`select to_char(a, '99D90') test1, to_char(a, '90D90') test2, to_char(a, 'FM90D99') test3, rtrim(to_char(a, 'FM90D99'), to_char(0, 'D')) test4 from ( select 50 a from dual union all select 50.57 from dual union all select 5.57 from dual union all select 0.35 from dual union all select 0.4 from dual union all select 0.80 from dual union all select .88 from dual ) order by a; `

returns:

`TEST1 TEST2 TEST3 TEST4 ------ ------ ------ ------ .35 0.35 0.35 0.35 .40 0.40 0.4 0.4 .80 0.80 0.8 0.8 .88 0.88 0.88 0.88 5.57 5.57 5.57 5.57 50.00 50.00 50. 50 50.57 50.57 50.57 50.57 `

As you can see, what may work in one case doesn't in another. Do I have to test for whole numbers, decimal numbers, then leading and trailing zeros and apply a different formatter for each? Is there just a simple cast-like construct? (tried cast too btw, similar issues).

--------------Solutions-------------

`0.4`

, `.4`

and `0.4000`

are all the same. It is just the way you want to display the number.

As I understand, you want to preserve the rows as **string**, and not just **display** them. Then you could handle the conditions using a **CASE** expression.

**TRUNC(NUMBER)** truncates the decimal portion and gives you a whole number. This will be the driving condition.

So, when `TRUNC(NUMBER)`

= 0, it means it has only decimal part, and we need to append a zero in the beginning, else just convert it into text.

For example,

`SQL> WITH DATA AS(`

2 select 50 a from dual

3 union all select 50.57 from dual

4 union all select 5.57 from dual

5 union all select 0.35 from dual

6 union all select 0.4 from dual

7 UNION ALL SELECT 0.80 FROM dual

8 UNION ALL SELECT .88 FROM dual

9 )

10 SELECT

11 CASE

12 WHEN TRUNC(A) = 0

13 THEN ltrim(to_char(a, '0D99'), ' ')

14 ELSE TO_CHAR(a)

15 END text

16 FROM DATA;

```
```TEXT

----------------------------------------

50

50.57

5.57

0.35

0.40

0.80

0.88

7 rows selected.

`SQL>`