Showing 1234.567 as $1,234.57, -1234.57 as <1234.57> in an accounting report, or 255 as FF takes a number format model. TO_CHAR uses one to turn a number into text, and TO_NUMBER uses one to read formatted text back into a number. The elements are few, but the details, such as leading zeros, signs, and separators that depend on the session, decide whether a report looks right.
Code for This Guide
The main examples are in the examples/format-models folder of the Oracle Database 26ai code repository on GitHub, each with its output. The examples need no tables; the repository also has the NIMBUS sample schema in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Elements
| Element | Meaning | Example |
|---|---|---|
| 9 | A digit; leading zeros are left out | 9999 gives 123 with a leading blank |
| 0 | A digit, printed even as a leading or trailing zero | 0999 gives 0123 |
| , . | Group separator and decimal point | 9,999.99 |
| G D | Group separator and decimal character of NLS_NUMERIC_CHARACTERS | 9G999D99 |
| $ | A dollar sign | $999 |
| L C U | Local currency symbol, ISO currency code, dual currency symbol | L999 |
| MI | Trailing minus sign for negative numbers | 999MI gives 42- |
| PR | Angle brackets around negative numbers | 999PR gives <42> |
| S | Leading or trailing + or - sign | S999 gives +42 |
| EEEE | Scientific notation | 9.99EEEE gives 1.23E+03 |
| V | Multiplies by 10 to the power of the 9s after it | 99V99 gives 4200 for 42 |
| X | Hexadecimal digits | XX gives FF for 255 |
| RN, rn | Roman numerals | RN gives MMXXVI for 2026 |
| TM, TM9, TME | The shortest text form | TM |
| B | Blanks for a zero integer part | B9999 |
| FM | No leading or trailing blanks | FM999.99 |
Twelve Models Side by Side
This example formats -1234.567 and 0.5 with twelve models, built as rows with a VALUES constructor.
Example:
select fmt, to_char(-1234.567, fmt) as negative, to_char(0.5, fmt) as half
from (values ('9999.99'), ('0999.99'), ('9,999.99'), ('9G999D99'), ('$9,999.99'),
('L9,999.99'), ('9999.99MI'), ('9999.99PR'), ('S9999.99'),
('9.99EEEE'), ('FM9999.999'), ('99')) t (fmt);Output:
FMT NEGATIVE HALF _____________ ______________________ ______________________ 9999.99 -1234.57 .50 0999.99 -1234.57 0000.50 9,999.99 -1,234.57 .50 9G999D99 -1,234.57 .50 $9,999.99 -$1,234.57 $.50 L9,999.99 -$1,234.57 $.50 9999.99MI 1234.57- .50 9999.99PR <1234.57> .50 S9999.99 -1234.57 +.50 9.99EEEE -1.23E+03 5.00E-01 FM9999.999 -1234.567 .5 99 ### 1 12 rows selected.
Several rules are visible in the output:
- Without FM, the result is padded with leading blanks, including one reserved for the sign, so columns line up.
- Decimals beyond the model are rounded: -1234.567 shows as -1234.57, and 0.5 with model 99 shows as 1.
- A number with more integer digits than the model allows is shown as # characters, as -1234.567 with model 99.
- L and $ differ: L uses the session's currency symbol, which is $ here but could be anything.
Keep the Leading Zero
With 9 in front of the decimal point, 0.5 shows as .50. A 0 just before the decimal point keeps the zero; adding FM removes the padding as well.
Example:
-- 9 drops the leading zero of 0.5; a 0 just before the decimal point keeps it
select to_char(0.5, '990.99') as with_zero,
to_char(0.5, 'FM990.00') as trimmed,
to_char(255, 'FMXX') as hex,
to_char(2026, 'FMRN') as roman
from dual;Output:
WITH_ZERO TRIMMED HEX ROMAN ____________ __________ ______ _________ 0.50 0.50 FF MMXXVI
Read Formatted Text with TO_NUMBER
The same elements work in reverse. TO_NUMBER with a matching model reads currency symbols, separators, trailing signs, brackets, and hexadecimal digits.
Example:
-- reading formatted text back into numbers
select to_number('$1,234.57', '$9,999.99') as dollars,
to_number('1234.57-', '9999.99MI') as trailing_minus,
to_number('<42>', '99PR') as brackets,
to_number('FF', 'XX') as from_hex
from dual;Output:
DOLLARS TRAILING_MINUS BRACKETS FROM_HEX __________ _________________ ___________ ___________ 1234.57 -1234.57 -42 255
Separators and Currency by Locale
G, D, and L follow the session's NLS parameters: NLS_NUMERIC_CHARACTERS for the decimal and group characters and NLS_CURRENCY for L. TO_CHAR and TO_NUMBER also accept them as a last argument for one conversion. Because G can appear anywhere, 9G99G99G999 formats the Indian system of lakhs and crores.
Example:
select to_char(1234567.891, '9G999G999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') as german,
to_char(1234567.891, '9G99G99G999D99') as indian,
to_char(date '2026-03-15', 'Day DD Month YYYY', 'NLS_DATE_LANGUAGE = German') as de
from dual;Output:
GERMAN INDIAN DE ________________ __________________ _______________________________ 1.234.567,89 12,34,567.89 Sonntag 15 März 2026
Use G and D rather than comma and period in applications used across countries, so each user sees their own separators. Use comma and period when the output must be fixed, such as a file for another system.
Related Guides
- Oracle TO_CHAR (Number) Function: A Simple Guide to Formatting Numbers
- Oracle Datetime Format Models Explained
Conclusion
A number format model combines 9 and 0 for digits, separators, currency symbols, and sign elements such as MI, PR, and S. 9 drops leading zeros and 0 keeps them, extra decimals are rounded, too many integer digits show as #, FM removes padding, and G, D, and L follow the session's language settings. TO_NUMBER reads text back with the same elements.
