Purpose
This function converts a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE value to a VARCHAR2 value in the specified numeric format.
Syntax
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
Parameters
Parameter |
Description |
|---|---|
| n | The value of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type. Note If n is a negative value, the negative sign is displayed at the beginning of the output value. For example, TO_CHAR(-1,'$9') returns -$1, not $-1. |
| fmt | The output format parameter, which is optional. If you omit fmt, n is converted to a VARCHAR2 value. For more information about the values of this parameter, see the parameter list. |
| nlsparam | Defines the decimal character, group separator, local currency symbol, and international currency symbol. This parameter is optional. |
fmt Parameter list
fmt Parameter Value |
Description |
|---|---|
| 9 | Returns a value with the specified number of digits. |
| 0 | Returns leading zeros. |
| ,(comma) | Returns a comma at the specified position. You can specify multiple commas in the number format model. Limitation : The number format model cannot start with a comma, and commas cannot appear to the right of the decimal character or the period. |
| .(period) | Returns a decimal number with the decimal point at the specified position. Limitation : You can specify only one decimal point in the number format model. |
Return type
VARCHAR2
Examples
Convert 123456789.123 to the 999,999,999 format.
obclient> SELECT TO_CHAR(123456789.123,'999,999,999') FROM DUAL;
+--------------------------------------+
| TO_CHAR(123456789.123,'999,999,999') |
+--------------------------------------+
| 123,456,789 |
+--------------------------------------+
1 row in set