Number Format Elements

A number format model is composed of one or more number format elements. Table 2-13 lists the elements of a number format model. Examples are shown in Table 2-14.

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

Table 2-13 Number Format Elements

Element	Example	Description
, (comma)	`9,999`	Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions: A comma element cannot begin a number format model. A comma cannot appear to the right of a decimal character or period in a number format model.
. (period)	`99.99`	Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model.
$	`$9999`	Returns value with a leading dollar sign.
0	`0999` `9990`	Returns leading zeros. Returns trailing zeros.
9	`9999`	Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B	`B9999`	Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model).
C	`C999`	Returns in the specified position the ISO currency symbol (the current value of the `NLS_ISO_CURRENCY` parameter).
D	`99D99`	Returns in the specified position the decimal character, which is the current value of the `NLS_NUMERIC_CHARACTER` parameter. The default is a period (.). Restriction: You can specify only one decimal character in a number format model.
EEEE	`9.9EEEE`	Returns a value using in scientific notation.
FM	`FM90.9`	Returns a value with no leading or trailing blanks.
G	`9G999`	Returns in the specified position the group separator (the current value of the `NLS_NUMERIC_CHARACTER` parameter). You can specify multiple group separators in a number format model. Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L	`L999`	Returns in the specified position the local currency symbol (the current value of the `NLS_CURRENCY` parameter).
MI	`9999MI`	Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model.
PR	`9999PR`	Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restriction: The PR format element can appear only in the last position of a number format model.
RN rn	`RN` `rn`	Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
S	`S9999` `9999S`	Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+). Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model.
TM	`TM`	"Text minimum". Returns (in decimal output) the smallest number of characters possible. This element is case-insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, then Oracle automatically returns the number in scientific notation. Restrictions: You cannot precede this element with any other element. You can follow this element only with 9 or E (only one) or e (only one).
U	`U9999`	Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the `NLS_DUAL_CURRENCY` parameter).
V	`999V99`	Returns a value multiplied by 10ⁿ (and if necessary, round it up), where `n` is the number of 9's after the "V".
X	`XXXX` `xxxx`	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle rounds it to an integer. Restrictions: This element accepts only positive values or 0. Negative values return an error. You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has 1 leading blank.

Table 2-14 shows the results of the following query for different values of number and 'fmt':

SELECT TO_CHAR(number, 'fmt')
   FROM DUAL;

Table 2-14 Results of Example Number Conversions

number	'fmt'	Result
`-1234567890`	`9999999999S`	`'1234567890-'`
`0`	`99.99`	`' .00'`
`+0.1`	`99.99`	`' .10'`
`-0.2`	`99.99`	`' -.20'`
`0`	`90.99`	`' 0.00'`
`+0.1`	`90.99`	`' 0.10'`
`-0.2`	`90.99`	`' -0.20'`
`0`	`9999`	`' 0'`
`1`	`9999`	`' 1'`
`0`	`B9999`	`' '`
`1`	`B9999`	`' 1'`
`0`	`B90.99`	`' '`
`+123.456`	`999.999`	`' 123.456'`
`-123.456`	`999.999`	`'-123.456'`
`+123.456`	`FM999.009`	`'123.456'`
`+123.456`	`9.9EEEE`	`' 1.2E+02'`
`+1E+123`	`9.9EEEE`	`' 1.0E+123'`
`+123.456`	`FM9.9EEEE`	`'1.2E+02'`
`+123.45`	`FM999.009`	`'123.45'`
`+123.0`	`FM999.009`	`'123.00'`
`+123.45`	`L999.99`	`' $123.45'`
`+123.45`	`FML999.99`	`'$123.45'`
`+1234567890`	`9999999999S`	`'1234567890+'`