Number format models specify the format of fixed-point and floating-point numbers stored in the database. When you need to convert a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE value in an SQL statement to the VARCHAR2 data type, you can use the number format model in the function.
Number format models in functions
You can use number format models in the following functions:
When
NUMBER,BINARY_FLOAT, orBINARY_DOUBLEappears in an expression, condition, SQL function, or SQL statement, you can use the parameters of theTO_CHARfunction to specify the format of these values when you need to convert them to theVARCHAR2data type.When
CHARorVARCHAR2appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of theTO_NUMBERfunction (excludingNLS_NUMERIC_CHARACTERS) to specify the format of these values when you need to convert them to theNUMBERdata type.When
CHARorVARCHAR2appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of theTO_BINARY_FLOATandTO_BINARY_DOUBLEfunctions to specify the format of these values when you need to convert them to theBINARY_FLOATorBINARY_DOUBLEdata type.
Number format models round numbers and retain a specified number of significant digits. If the number of decimal places in a value exceeds the number of decimal places specified in the format, the value is replaced with a #.
If a positive NUMBER value is too large to be represented in the specified format, it is replaced with the infinity symbol (~). If a negative NUMBER value is too small to be represented in the specified format, it is replaced with the negative infinity symbol (-~).
Elements of the numeric format model
Number format elements
A number format model consists of one or more number format elements. Negative return values automatically include a leading minus sign, and positive return values automatically include a leading space, unless the format model includes elements such as MI, S, or PR that indicate the sign of the number.
The following table lists the elements that can be included in a number format model and their specific usage.
| Element | Example | Description |
|---|---|---|
| , (comma) | 9,999 | Returns a comma at the specified position. You can specify multiple commas in a number format model. Limitations: A comma element cannot appear at the beginning of a number format model. In a number format model, a comma cannot appear to the right of a decimal point or a period. |
| . (decimal point) | 99.99 | Returns a decimal point, that is, a period (.) at the specified position. Limitations: 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 leading zeros. Leading zeros are usually represented as spaces, except when the formatted value is 0, in which case leading zeros are represented as character zeros. Returns trailing zeros. |
| 9 | 9999 | Returns the specified number of digits. If the formatted value is a positive number, the result contains leading spaces. Otherwise, the result contains a leading minus sign. |
| B | B9999 | Returns a space when the result is zero. |
| C | C999 | Returns the ISO currency symbol (the value represented by the current NLS_ISO_CURRENCY parameter) at the specified position. |
| D | 99D99 | Functions the same as "." but 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 | Acts like the "." element, but uses the default value of the NLS_NUMERIC_CHARACTER parameter. |
| L | L999 | Returns the local currency symbol at the specified position, using the current value of the NLS_CURRENCY parameter. |
| Mi | 9999MI | Returns a negative value with a trailing minus sign (-) and a positive value with a trailing space. Limitations: The Mi format element can only appear at the end of a numeric format model. |
| PR | 9999PR | Returns a negative value enclosed in angle brackets (<>) and a positive value with leading and trailing spaces. Limitations: The PR format element can only appear at the end of a numeric format model. |
| RN(rn) | RN rn | Returns the value in uppercase Roman numerals or lowercase Roman numerals. Limitations: The value can be an integer between 1 and 3999. |
| S | S9999 9999S |
|
| TM | TME TM9 | The default value is TM9. If the output is less than 64 characters, it returns the number with fixed symbols. If the output exceeds 64 characters, it automatically returns the number in scientific notation. When the number has more than 64 digits, the output of TM9 is the same as that of TME. |
| U | U9999 | Returns the euro (or other) dual currency symbol at the specified position, determined by the current value of the NLS_dual_currency parameter. |
| V(v) | 999V99 | Returns the value multiplied by 10n (rounded if necessary), where n is the number of 9s following the 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 the nearest integer. Limitations:
|
| FM(fm) | FM999 | Removes leading spaces. |
Example of element formatting
The following table shows the conversion results of different values using the fmt parameter of the formatting element by executing the SQL command SELECT TO_CHAR(number, 'fmt') FROM DUAL;. If the format model parameter fmt is omitted, the value will be converted to a VARCHAR2 of sufficient length to retain all 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 two decimal places.
obclient> SELECT TO_CHAR(0, '99.99') FROM DUAL; +--------------------+ | TO_CHAR(0,'99.99') | +--------------------+ | .00 | +--------------------+ 1 row in setOmit the
fmtparameter 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 setWhen the result of
TO_CHARexceeds 40 bytes, or when the input parameter is of typeBINARY_DOUBLEorBINARY_FLOAT, the result is converted to scientific notation.obclient> SELECT TO_CHAR(12355555555555555555555555555555555555555555555555) FROM DUAL; +-------------------------------------------------------------+ | TO_CHAR(12355555555555555555555555555555555555555555555555) | +-------------------------------------------------------------+ | 1.2355555555555555555555555555555556E+49 | +-------------------------------------------------------------+ 1 row in set