Oracle: Need number values converted to text AND preserved - resolved

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>

Category:sql Time:2018-07-26 Views:1

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 1.297 (s). 13 q(s)