CODEWITHSUNDEEP
sqloracle-databaseformattingoracle12cdecimalformat
user1351848
user1351848

Reputation: 137

Oracle: Need number values converted to text AND preserved

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).

Solved

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.

Upvotes: 1

Views: 923

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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>

Upvotes: 1

Related Questions