How-to: Oracle Number formats

A number format can be used as part a TO_CHAR or TO_DATE function to return a number in the specified format.

Format Description
9 Number
(suppress leading/trailing zeros)
Includes a leading - for negative no's or one leading space for pos no's

9999=' 123'
0 Number
including leading/trailing zeros
FM Supress all leading /trailing blanks FM9999=123
$ Include a leading $ $999=$123
B Over-ride the '0' format and replace leading 0's with blank spaces ' ' B9999=' 123'
S Include poth positive+ and negative - signs S999 =+123
999S =123+
PR Indicate SIGN with <brackets> PR999=<123>
PR999=' 123 '
MI Indicate Minus SIGN 999MI=123-
RN or rn Roman numeral
(upper or lower case)
D Position of decimal point(.) 99D99=12.34
G Group separator (often a comma) in desired position 9G999=1,234
, Return a comma in desired position 9,999=1,234
. Return a period in desired position 99.99=12.34
C ISO currency symbol C99
L Local currency symbol L99

Return a value using in scientific notation

9.9EEEE= 1.2E+02
TM Text Minimum
TM9(the default) or TME (Scientific notation)
U Returns in the specified position the Euro (or other) dual currency symbol.
U9999 = €1234
V Return a value multiplied by 10n , where n is the number of 9's after the V.
(value will be rounded up if necessary)
999V99= 123 x 1045
X Returns the hexadecimal value of the specified number of digits. select to_char('64','xx') from dual;

Some of the format elements above can only be used at the start or end of a format string:

The default text minimum number format model is TM9, which returns the number in fixed notation unless the output exceeds 64 characters in which case the number will be returned in scientific notation.


Date & Time Formats

Copyright © 1999-2022
Some rights reserved