Number format models specify the formats of fixed-point and floating-point numbers stored in a database. To convert a NUMBER, BINARY_FLOAT, or BINARY_ DOUBLE value in SQL statements into VARCHAR2 data, you can use number format models in functions.
Number format models in functions
Number format models can be used in the following functions:
To convert a value of the
NUMBER,BINARY_FLOATorBINARY_ DOUBLEdata type in an expression, a condition, an SQL function, or an SQL statement into theVARCHAR2data type, you can use a parameter of theTO_CHARfunction to specify the format of the value.To convert a value of the
CHARorVARCHAR2data type in an expression, a condition, an SQL function, or an SQL statement to theNUMBERdata type, you can use a parameter of theTO_NUMBERfunction (NLS_NUMERIC_CHARACTERSis not supported) to specify the format of the value.To convert a value of the
CHARorVARCHAR2data type in an expression, a condition, an SQL function, or an SQL statement to theBINARY_FLOATorBINARY_DOUBLEdata type, you can use a parameter of theTO_BINARY_FLOATorTO_BINARY_DOUBLEfunction to specify the format of the value.
A number format model rounds a value to the specified number of significant digits. If a value has more significant digits on the left of the decimal place than specified in the format, the value is replaced with a number sign (#).
If a positive NUMBER value is extremely large and cannot be represented in the specified format, the value is replaced with an infinity sign (∞). If a negative NUMBER value is extremely small and cannot be represented by the specified format, the value is replaced with a negative infinity sign (-∞).
Elements of a number format model
Number format element
A number format model consists of one or more number format elements. A negative return value automatically contains the leading minus sign (-), and a positive return value automatically contains the leading space, unless the format model contains a format element that represents positive or negative, such as MI, S, or PR.
The following table describes the elements contained in number format models and their usage.
| Number format element | Example | Description |
|---|---|---|
| Comma (,) | 9,999 | Returns a comma in the specified position. You can specify multiple commas in a number format model. Restriction: A number format model cannot start with a comma. A comma cannot appear to the right of a decimal point or period in a number format model. |
| Decimal point (.) | 99.99 | Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one decimal point in a number format model. |
| $ | $9999 | Returns a value with a leading dollar sign. |
| 0 | 0999 9990 | Returns the leading zeros. Leading zeros are usually represented as spaces. However, when the formatted value is zero, the leading zeros are 0s. Returns the trailing zeros. |
| 9 | 9999 | Returns a result with the specified number of digits. The result has a leading space if the formatted value is positive or a leading minus sign (-) if otherwise. |
| B | B9999 | Returns spaces when the result is zero. |
| C | C999 | Returns the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter) in the specified position. |
| D | 99D99 | This element works the same as a decimal point (.) and can appear only once. The difference is that this element uses the default value of the NLS_NUMERIC_CHARACTER parameter. |
| EEEE | 9.9EEEE | Returns a value in scientific notation. |
| G | 9G999 | This element works in the same way as a decimal point (.). The difference is that this element uses the default value of the NLS_NUMERIC_CHARACTER parameter. |
| L | L999 | Returns the local currency symbol and uses the current value of the NLS_CURRENCY parameter in the specified position. |
| MI | 9999MI | Returns a negative value with a trailing minus sign (-). Returns positive value with a trailing space. Restriction: The MI format element can appear only in the last position of a number format model. |
| PR | 9999PR | Returns a negative value in a pair of angle brackets (<>). Returns a positive value with a leading space and a trailing space. 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 uppercase Roman numerals. Returns a value as lowercase Roman numerals. Restriction: The value can be an integer from 1 to 3999. |
| S | S9999 9999S |
|
| TM | TME TM9 | The default value is TM9. A number with a fixed sign is returned unless the output exceeds 64 characters. If the output exceeds 64 characters, the number is returned automatically in scientific notation. When the output exceeds 64 bits, the output of TM9 is equivalent to the output of TME. |
| U | U9999 | Returns the Euro (or other) dual currency symbol represented by the current value of the NLS_dual_currency parameter in the specified position. |
| V(v) | 999V99 | Returns a value multiplied by 10n (rounded if necessary), where n is the quantity of 9s after V. |
| X(x) | XXXX xxxx | Returns a hexadecimal value with the specified number of digits. If the specified number is not an integer, it is rounded to an integer. Limitations:
|
| FM(fm) | FM999 | Removes the leading space. |
Conversion of formatted elements
The following table lists the values and the corresponding results converted based on the fmt argument by executing the SELECT TO_CHAR(number, 'fmt') FROM DUAL statement. If you omit the fmt argument, the value is converted into a VARCHAR2 value that is long enough to hold its significant digits.
| Value | 'fmt' | Result |
|---|---|---|
| 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' |
| +123.456 | 999.999 | '123.456' |
| -123.456 | 999.999 | '-123.456' |
Examples
Convert the number 0 to a value with a decimal point, with two significant digits after the decimal point.
obclient> SELECT TO_CHAR(0, '99.99') FROM DUAL; +--------------------+ | TO_CHAR(0,'99.99') | +--------------------+ | .00 | +--------------------+ 1 row in setOmit the
fmtargument and retain all significant digits of the value.obclient> SELECT TO_CHAR(123.456) FROM DUAL; +------------------+ | TO_CHAR(123.456) | +------------------+ | 123.456 | +------------------+ 1 row in setIf
TO_CHARreturns a value that is longer than 40 bytes or the input parameter is of theBINARY_DOUBLEorBINARY_FLOATdata type, the result is converted into scientific notation.obclient> SELECT TO_CHAR(12355555555555555555555555555555555555555555555555) FROM DUAL; +-------------------------------------------------------------+ | TO_CHAR(12355555555555555555555555555555555555555555555555) | +-------------------------------------------------------------+ | 1.2355555555555555555555555555555556E+49 | +-------------------------------------------------------------+ 1 row in set