Purpose
TO_CHAR(number) converts a value of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to a value of the VARCHAR2 data type in the specified format.
Syntax
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
Parameters
| Parameter | Description |
|---|---|
| n | A value of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type. Note: If n is negative, this function returns a value with a leading minus sign (-). For example, TO_CHAR(-1,'$9') returns -$1 instead of $-1. |
| fmt | The format of the return value. This parameter is optional. If you do not specify fmt, n is converted to a VARCHAR2 value. For information about values for fmt, see "Values for fmt". |
| nlsparam | The decimal character, group separator, local currency symbol, and international currency symbol. This parameter is optional. |
Values for fmt
| 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 a number format model. Restriction: A number format model cannot start with a comma (,) and a comma (,) cannot appear to the right of a decimal character or period (.). |
| . (decimal point) | Returns a decimal number with the decimal point at the specified position. Restriction: You can specify only one decimal point in a number format model. |
Return type
The return type is VARCHAR2.
Examples
The following example converts 123456789.123 to a value in 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
