Number format models specify the format of fixed-point and floating-point numbers stored in the database. When you need to convert the values of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data types in an SQL statement to the VARCHAR2 data type, you can use the number format models 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 converting 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 converting 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 converting them to theBINARY_FLOATorBINARY_DOUBLEdata types.
Number format models round the values and retain the 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 a 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 values automatically include a leading space, unless the format model includes elements such as MI, S, or PR that indicate the sign of a number.
The following table lists the elements 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 at the specified position. Limitation: You can specify only one decimal point in a number format model. |
| $ | $9999 | Returns the 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 a result with the specified number of digits. If the formatted value is a positive number, the result contains leading spaces. Otherwise, the result contains a leading negative sign. |
| B | B9999 | Returns a space when the result is zero. |
| C | C999 | Returns the ISO currency symbol (the value specified by the 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 the value in scientific notation. |
| G | 9G999 | The behavior is the same as the behavior of 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. This element uses the 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. Limitation: The Mi format element can appear only 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. Limitation: The PR format element can appear only at the end of a numeric format model. |
| RN(rn) | RN rn | Returns the value in uppercase Roman numerals or lowercase Roman numerals. Limitation: The value can be an integer between 1 and 3999. |
| S | S9999 9999S |
|
| TM | TME TM9 | The default value is TM9. If the output does not exceed 64 characters, the number is returned with a fixed symbol. If the output exceeds 64 characters, the number is returned in scientific notation. When the number has more than 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 element. |
| 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 when you execute the SQL command SELECT TO_CHAR(number, 'fmt') FROM DUAL; for different numbers with the format element parameter fmt. If you omit the format model parameter fmt, 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 value 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 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 the result of
TO_CHARexceeds 40 bytes or 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