The TO_CHAR function converts the numeric value n of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE type to a value of the varchar2 data type based on the specified numeric format fmt. If you omit fmt, n is converted to a VARCHAR2 value and the value is long enough to hold valid digits. If n is negative, the negative sign is displayed on the leftmost side of the output value. For example, TO_CHAR(-1,'$9') returns -$1 instead of $-1.
Syntax
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
Parameters
| Parameter | Description |
|---|---|
| n | The expression of exact numeric data types or approximate numeric data types. The numeric data type can be NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. |
| fmt | The output format parameter. |
| nlsparam | The supported language is obtained from sys.V_$NLS_VALID_VALUES. |
fmt parameter list
| fmt parameter | Description |
|---|---|
| 9 | Returns the value for which the number of digits is specified. |
| 0 | It returns leading zeros. It returns trailing zeros. |
| , (Comma) | Returns the comma in the specified position. You can specify multiple commas when you format the number. Limits : You cannot start to format a numeric value from a comma, and the comma cannot appear to the right of a decimal character or a period. |
| . ( Decimal point) | Returns a decimal where the decimal point is in the specified position. Limits : You can specify only one decimal point when you format the number. |
Return type
VARCHAR2 data type
Examples
Execute the following statement:
SELECT TO_CHAR(123.456,'999') FROM DUAL;
The following query result is returned:
+------------------------+
| TO_CHAR(123.456,'999') |
+------------------------+
| 123 |
+----------------------