You can use number format models in the following functions:
-
In the
TO_CHAR
function to translate a value ofNUMBER
,BINARY_FLOAT
, orBINARY_DOUBLE
datatype toVARCHAR2
datatype -
In the
TO_NUMBER
function to translate a value ofCHAR
orVARCHAR2
datatype toNUMBER
datatype -
In the
TO_BINARY_FLOAT
andTO_BINARY_DOUBLE
functions to translateCHAR
andVARCHAR2
expressions toBINARY_FLOAT
orBINARY_DOUBLE
values
All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. This event typically occurs when you are using TO_CHAR
with a restrictive number format string, causing a rounding operation.
-
If a positive
NUMBER
value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negativeNUMBER
value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~). -
If a
BINARY_FLOAT
orBINARY_DOUBLE
value is converted toCHAR
orNCHAR
, and the input is either infinity orNaN
(not a number), then Oracle always returns the pound signs to replace the value.
Number Format Elements
A number format model is composed of one or more number format elements. The tables that follow list the elements of a number format model and provide some examples.
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) |
| Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
. (period) |
| Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model. |
$ |
| Returns value with a leading dollar sign. |
0 |
| Returns leading zeros. Returns trailing zeros. |
9 |
| 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 |
| Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). |
| Returns in the specified position the ISO currency symbol (the current value of the | |
| Returns in the specified position the decimal character, which is the current value of the Restriction: You can specify only one decimal character in a number format model. | |
| Returns a value using in scientific notation. | |
| Returns in the specified position the group separator (the current value of the Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. | |
| Returns in the specified position the local currency symbol (the current value of the | |
MI |
| 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 |
| 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 |
| 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 |
| 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 |
| The text minimum number format model 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 the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation. Restrictions:
|
| Returns in the specified position the Euro (or other) dual currency symbol (the current value of the | |
V |
| Returns a value multiplied by 10n (and if necessary, round it up), where |
| Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer. Restrictions:
|
shows the results of the following query for different values of number
and 'fmt'
:
Results of Number Conversions
number | 'fmt' | Result |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|