Search code examples
oracle-databaseformatdata-conversion

oracle TO_CHAR format (decimal always required)

I need to convert my oracle NUMBER into a string with this format: 999,999

I'm trying with TO_CHAR but I'm not having the correct output.

This is the expected behavior:

9 ---> 9,000
9,88 --> 9,880
0 --> 0,000
-1 --> -1,000
80 --> 80,000
Solution

  • Just use a format mask with TO_CHAR - if you are using , as decimal character:

    TO_CHAR(-1, 'FM999G999G990D000') -> -1,000
TO_CHAR(9.88, 'FM999G999G990D000') -> 9,880
...

    And make sure your format mask is long enough to fit for all possible length of the numeric string.