Let’s take a look at the Oracle numeric format masks.
Oracle numeric format table
| Format Element | Description of Element | Format | Number | Character Result |
| 9 | Numeric width | 9999 | 12 | 12 |
| 0 | Displays leading zeros | 09999 | 0012 | 00012 |
| . | Position of decimal point | 09999.999 | 030.40 | 00030.400 |
| D | Decimal separator position (period is default) | 09999D999 | 030.40 | 00030.400 |
| , | Position of comma symbol | 09999,999 | 03040 | 00003,040 |
| G | Group separator position (comma is default) | 09999G999 | 03040 | 00003,040 |
| $ | Dollar sign | $099999 | 03040 | $003040 |
| L | Local currency | L099999 | 03040 | GBP003040 if nls_currency is set to GBP |
| MI | Position of minus sign for negavites | 99999MI | -3040 | 3040- |
| PR | Wrap negatives in parentheses | 99999PR | -3040 | <3040> |
| EEEE | Scientific notation | 99.99999EEEEE | 121.976 | 1.21976E+02 |
| U | nls_dual_currency | U099999 | 03040 | CAD003040 if nls_dual_currency is set to CAD |
| V | Multiplies by 10n times (n is the number of nines after V) | 9999V99 | 3040 | 304000 |
| S | + or – sign is prefixed | S999999 | 3040 | +3040 |
Example
select TO_CHAR(12,'S0999999') EXAMPLE from dual;
The result of this query is: +0000012