Number format models specify the format of fixed-point and floating-point numbers stored in the database. When you need to convert the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE values 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 the values. TheNLS_NUMERIC_CHARACTERSparameter is not supported.When
CHARorVARCHAR2appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of theTO_NUMBERfunction to specify the format of the values. TheNLS_NUMERIC_CHARACTERSparameter is not supported.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 the values.
Number format models round numbers and retain only the 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 the positive value of a NUMBER is too large to be represented in the specified format, the value is replaced with the infinity symbol (~). If the negative value of a NUMBER is too small to be represented in the specified format, the value 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 contains elements such as MI, S, or PR that indicate the sign of the number.
The following table lists the elements included in a number format model and their specific usage.
| Format 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 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. |
| . (period) | 99.99 | Returns a period at the specified position. Limitations: You can specify only one period 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 the character 0. Returns trailing zeros. |
| 9 | 9999 | Returns the specified number of digits. If the formatted value is positive, the result includes leading spaces. Otherwise, the result includes a leading minus sign. |
| B | B9999 | Returns a space when the result is zero. |
| C | C999 | Returns the ISO currency symbol at the specified position (the value of the current NLS_ISO_CURRENCY parameter). |
| D | 99D99 | Acts like a period, 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 | The same as the "." element, except that this element 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 within 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 or equal to 64 characters, the number is returned with a fixed sign. If the output exceeds 64 characters, the number is automatically returned in scientific notation. When the number exceeds 64 digits, the output of TM9 is the same as the output 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 a 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 formatting element conversion
The following table shows examples of converting different numbers using the fmt parameter of the formatting element. The results are obtained by executing the SQL command SELECT TO_CHAR(number, 'fmt') FROM DUAL;. If the format model parameter fmt is omitted, the number is converted to a VARCHAR2 of sufficient length to retain all its significant digits.
| Number | '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 and 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
fmtparameter to 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