The format mask and the NLS parameters are identical to the TO_NUMBER function.
The NLS parameters again are
NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
NLS_CURRENCY -- Specifies the local currency.
NLS_ISO_CURRENCY -- Character(s) to represent the ISO currency symbol.
The optional format string you may pass to TO_CHAR() has a number of parameters that affect the string returned by TO_CHAR().
Some of these parameters are listed in the following table.
Parameter
Format Examples
Description
9
999
Returns digits in specified positions with leading negative sign if the number is negative.
0
0999 9990
0999: Returns a number with leading zeros.9990: Returns a number with trailing zeros.
.
999.99
Returns a decimal point in the specified position.
,
9,999
Returns a comma in the specified position.
$
$999
Returns a leading dollar sign.
B
B9.99
If the integer part of a fixed point number is zero, returns spaces for the zeros.
C
C999
Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter.
D
9D99
Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character).
EEEE
9.99EEEE
Returns number using the scientific notation.
FM
FM90.9
Removes leading and trailing spaces from number.
G
9G999
Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter.
L
L999
Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter.
MI
999MI
Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space.
PR
999PR
Returns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces.
RN rn
RN rn
Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999.
S
S999 999S
S999: Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign.999S: Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign.
TM
TM
Returns a number using the minimum number of characters. Default is TM9, which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation.
U
U999
Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter.
V
99V99
Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded.
X
XXXX
Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer.
