Version: V1.0.0

TO_CHAR(number)

Description

This function converts numeric data to the VARCHAR data type.

Syntax

TO_CHAR(number [, fmt])

Parameters

number: the numeric value to be converted.
fmt: optional. The parameter used to specify the output format of the numeric value. If you omit fmt, the number is converted to a VARCHAR value. For more information, see fmt.

fmt

The fmt parameter can be set to the following values:

Numeric: 0 and 9.
Grouping: . , , , D , and G.
Currency: $ , C , L , and U.
[Computational conversion](#Conversion classes): EEEE , RN , V , and X.
[Sign](#Sign classes): MI , PR , and S.
[Traditional](#Traditional classes): TM.
[Other](#Other classes): B and FM.

Numeric

0: A placeholder that indicates a numeric position. If the corresponding character is not available, it is filled with 0.

tip

0 is a required symbol. If the corresponding character is not available, it is filled with 0. This is different from 9.

Example:

SELECT TO_CHAR(123, '00000'), TO_CHAR(123.456, '0000.0000');

The returned result is as follows:

+-----------------------+-------------------------------+
| TO_CHAR(123, '00000') | TO_CHAR(123.456, '0000.0000') |
+-----------------------+-------------------------------+
|  00123                |  0123.4560                    |
+-----------------------+-------------------------------+
1 row in set (0.001 sec)

9: A placeholder that indicates a numeric position. The following table describes the behavior of this placeholder.
- If the placeholder is in the decimal part, it indicates that the corresponding character is to be converted. If the corresponding character is not available, it is filled with 0.
- If the placeholder is in the integer part, it indicates that no character is to be filled.
  
  Example:
```
SELECT TO_CHAR(123, '999999'), TO_CHAR(123.456, '9999.9999');
```
  The returned result is as follows:
```
+------------------------+-------------------------------+
| TO_CHAR(123, '999999') | TO_CHAR(123.456, '9999.9999') |
+------------------------+-------------------------------+
|     123                |   123.4560                    |
+------------------------+-------------------------------+
1 row in set (0.000 sec)
```

Grouping

. (period): A placeholder that indicates a decimal point. If the decimal part is insufficient, it is filled with zeros. If the decimal part exceeds the number of digits specified in fmt, it is rounded.

tip

fmt can contain only one decimal point.

Example:

SELECT TO_CHAR(123.555, '999.99'), TO_CHAR(123.5, '999.99');

The returned result is as follows:

+----------------------------+--------------------------+
| TO_CHAR(123.555, '999.99') | TO_CHAR(123.5, '999.99') |
+----------------------------+--------------------------+
|  123.56                    |  123.50                  |
+----------------------------+--------------------------+
1 row in set (0.001 sec)

, (comma): A placeholder that indicates a thousand separator. It can also be used as a decimal separator. The number of commas depends on the size of the number.

tip

The comma cannot be placed at the beginning of fmt and can appear only in the integer part.

Example:

SELECT TO_CHAR(123456, '99,99,99'), TO_CHAR(123456789, '999,999,999');

The returned result is as follows:

+-----------------------------+-----------------------------------+
| TO_CHAR(123456, '99,99,99') | TO_CHAR(123456789, '999,999,999') |
+-----------------------------+-----------------------------------+
|  12,34,56                   |  123,456,789                      |
+-----------------------------+-----------------------------------+
1 row in set (0.001 sec)

D (ISO decimal separator): The international version of the period (.). It functions the same as . but uses the default value of the NLS_NUMERIC_CHARACTER parameter.

tip

Unless required, do not use this format symbol or change the value of the NLS_NUMERIC_CHARACTER parameter.

Example:

SELECT TO_CHAR(123.555, '999D99'), TO_CHAR(123.5, '999D99');

The returned result is as follows:

+----------------------------+--------------------------+
| TO_CHAR(123.555, '999D99') | TO_CHAR(123.5, '999D99') |
+----------------------------+--------------------------+
|  123.56                    |  123.50                  |
+----------------------------+--------------------------+
1 row in set (0.000 sec)

G (ISO thousand separator): The international version of the comma (,). It can appear in multiple places.

tip

If you want to convert a decimal point, you must use it with D. You cannot use it with the period (.).

Example:

SELECT TO_CHAR(123456, '99G99G99'), TO_CHAR(123456789, '999G999G999');

The returned result is as follows:

+-----------------------------+-----------------------------------+
| TO_CHAR(123456, '99G99G99') | TO_CHAR(123456789, '999G999G999') |
+-----------------------------+-----------------------------------+
|  12,34,56                   |  123,456,789                      |
+-----------------------------+-----------------------------------+
1 row in set (0.000 sec)

Currency

$ (dollar sign): A placeholder that indicates a dollar sign. The value is returned with a leading dollar sign.

tip

$ can be placed anywhere in fmt, but it can appear only once.

Example:

SELECT TO_CHAR(123.45, '$999.99'), TO_CHAR(123.45, '9,9,9,9.$99');

The returned result is as follows:

+----------------------------+--------------------------------+
| TO_CHAR(123.45, '$999.99') | TO_CHAR(123.45, '9,9,9,9.$99') |
+----------------------------+--------------------------------+
|  $123.45                   |    $1,2,3.45                   |
+----------------------------+--------------------------------+
1 row in set (0.001 sec)

C (international currency symbol): A placeholder that indicates an international currency symbol. The value is returned with the currency symbol specified by the current value of the NLS_ISO_CURRENCY parameter.

tip
C can appear only in the first position of the integer part of fmt.

Example:
```
SELECT TO_CHAR(123.45, 'C999.99');
```
The returned result is as follows:
```
+----------------------------+
| TO_CHAR(123.45, 'C999.99') |
+----------------------------+
|      USD123.45             |
+----------------------------+
1 row in set (0.001 sec)
```

L (local currency symbol): A placeholder that indicates a local currency symbol. The value is returned with the local currency symbol. It can be placed at the beginning and end of the entire format.

Example:

SELECT TO_CHAR(123.45, 'L999.99'), TO_CHAR(123.45, '999.99L');

The returned result is as follows:

+----------------------------+----------------------------+
| TO_CHAR(123.45, 'L999.99') | TO_CHAR(123.45, '999.99L') |
+----------------------------+----------------------------+
|           $123.45          |           123.45$          |
+----------------------------+----------------------------+
1 row in set (0.000 sec)

U: A placeholder that indicates a dual currency symbol. The value is returned with the dual currency symbol specified by the current value of the NLS_DUAL_CURRENCY parameter.

Example:

SELECT TO_CHAR(123.45, 'U999.99'), TO_CHAR(123.45, '999.99U'), TO_CHAR(123.45, '999U999');

The returned result is as follows:

+----------------------------+----------------------------+----------------------------+
| TO_CHAR(123.45, 'U999.99') | TO_CHAR(123.45, '999.99U') | TO_CHAR(123.45, '999U999') |
+----------------------------+----------------------------+----------------------------+
|           $123.45          |           123.45$          |           123$450          |
+----------------------------+----------------------------+----------------------------+
1 row in set (0.001 sec)

Conversion classes

EEEE (scientific notation): returns a value in scientific notation. The number of 9s or 0s before the decimal point is not significant.

Example:

SELECT TO_CHAR(10000, '9EEEE'), TO_CHAR(10000, '9.9EEEE'), TO_CHAR(10000, '999.9EEEE');

The result is:

+-------------------------+---------------------------+-----------------------------+
| TO_CHAR(10000, '9EEEE') | TO_CHAR(10000, '9.9EEEE') | TO_CHAR(10000, '999.9EEEE') |
+-------------------------+---------------------------+-----------------------------+
|   1E+04                 |   1.0E+04                 |   1.0E+04                   |
+-------------------------+---------------------------+-----------------------------+
1 row in set (0.001 sec)

RN (rn): converts an integer (1 to 3999) to a Roman numeral. RN returns uppercase Roman numerals, and rn returns lowercase Roman numerals.

tip

The RN (rn) format element cannot be combined with other format elements.

Example:

SELECT TO_CHAR(99, 'RN'), TO_CHAR(99, 'rn');

The result is:

+-------------------+-------------------+
| TO_CHAR(99, 'RN') | TO_CHAR(99, 'rn') |
+-------------------+-------------------+
|            XCIX   |            xcix   |
+-------------------+-------------------+
1 row in set

V: returns a value multiplied by 10^n, where n is the number of 9s following the V element. If necessary, the value is rounded.

tip

The V format element cannot be used with decimal expressions in the fmt format, but it can be used with other symbols such as currency symbols.

Example:

SELECT TO_CHAR(123.456, '999V99'), TO_CHAR(123.4, '999V999'), TO_CHAR(5, '9V');

The result is:

+----------------------------+---------------------------+------------------+
| TO_CHAR(123.456, '999V99') | TO_CHAR(123.4, '999V999') | TO_CHAR(5, '9V') |
+----------------------------+---------------------------+------------------+
|  12346                     |  123400                   |  5               |
+----------------------------+---------------------------+------------------+
1 row in set (0.001 sec)

X: 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. The following restrictions apply:
- The element accepts only positive values or 0. Negative values return an error.
- You can add only 0 (to return leading zeros) or FM before the element. Any other value returns an error. If you use X without specifying 0 or FM, the returned value always has a leading space.
  
  Example:
```
SELECT TO_CHAR(10, 'X'), TO_CHAR(10, 'XXXX');
```
  The result is:
```
+------------------+---------------------+
| TO_CHAR(10, 'X') | TO_CHAR(10, 'XXXX') |
+------------------+---------------------+
|  A               |     A               |
+------------------+---------------------+
1 row in set (0.001 sec)
```

Sign classes

MI (mi): returns a negative value with a trailing minus sign (-). If the value is positive, a space is added at the end.

tip

The MI (mi) format element can appear only at the end of the number format model.

Example:

SELECT TO_CHAR(-123, '999MI'), TO_CHAR(-123, '999mi');

The result is:

+------------------------+------------------------+
| TO_CHAR(-123, '999MI') | TO_CHAR(-123, '999mi') |
+------------------------+------------------------+
| 123-                   | 123-                   |
+------------------------+------------------------+
1 row in set (0.000 sec)

PR: another way to express negative numbers:

If the number is positive, a space is added at the beginning.

If the number is negative, it is enclosed in angle brackets (<>).

tip

The PR format element can appear only at the end of the number format model.

Example:

SELECT TO_CHAR(-1234.89,'9G999D00PR'), TO_CHAR(1234.89,'9G999D00PR');

The result is:

+--------------------------------+-------------------------------+
| TO_CHAR(-1234.89,'9G999D00PR') | TO_CHAR(1234.89,'9G999D00PR') |
+--------------------------------+-------------------------------+
| <1,234.89>                     |  1,234.89                     |
+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

S: adds a plus sign (+) for positive numbers and a minus sign (-) for negative numbers.

tip

The S format element can appear only at the beginning or end of the number format model.

Example:

SELECT TO_CHAR(-123.45,'S999.99'), TO_CHAR(123.45,'S999.99');

The result is:

+----------------------------+---------------------------+
| TO_CHAR(-123.45,'S999.99') | TO_CHAR(123.45,'S999.99') |
+----------------------------+---------------------------+
| -123.45                    | +123.45                   |
+----------------------------+---------------------------+
1 row in set (0.001 sec)

Traditional classes

TM: returns the number in fixed notation by default, unless the output exceeds 64 characters. 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 that of TME.

Example:

SELECT TO_CHAR(1234, 'TM9'), TO_CHAR(1234, 'TME');

The result is:

+----------------------+----------------------+
| TO_CHAR(1234, 'TM9') | TO_CHAR(1234, 'TME') |
+----------------------+----------------------+
| 1234                 | 1.234E+03            |
+----------------------+----------------------+
1 row in set (0.000 sec)

Other classes

B (space): adds a space at the beginning of the integer part. It can appear in any position.

tip

The B format element can appear only in the integer part.

Example:

SELECT TO_CHAR(1234, '99B99'), TO_CHAR(1234, '9999B'), TO_CHAR(1234, 'B9999');

The result is:

+------------------------+------------------------+------------------------+
| TO_CHAR(1234, '99B99') | TO_CHAR(1234, '9999B') | TO_CHAR(1234, 'B9999') |
+------------------------+------------------------+------------------------+
|  1234                  |  1234                  |  1234                  |
+------------------------+------------------------+------------------------+
1 row in set (0.001 sec)

FM (fm): removes leading spaces.

tip

The FM format element can appear only at the beginning of the integer part in the fmt format.

Example:

SELECT TO_CHAR('  123.456', 'fm999.999'), TO_CHAR('  123.456', 'FM999.999');

The result is:

+-----------------------------------+-----------------------------------+
| TO_CHAR('  123.456', 'fm999.999') | TO_CHAR('  123.456', 'FM999.999') |
+-----------------------------------+-----------------------------------+
| 123.456                           | 123.456                           |
+-----------------------------------+-----------------------------------+
1 row in set (0.001 sec)

Return type

Returns data of the VARCHAR type.
Returns NULL if the number value is NULL.

Example

SELECT
    TO_CHAR(11111.111, '999,999.9999'),
    TO_CHAR(11111.111, '000,000.0000'),
    TO_CHAR(55555.555, '99,999.99'),
    TO_CHAR(55555.555, '99,999V99'),
    TO_CHAR(NULL, '999.999');

The returned result is as follows:

+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
| TO_CHAR(11111.111, '999,999.9999') | TO_CHAR(11111.111, '000,000.0000') | TO_CHAR(55555.555, '99,999.99') | TO_CHAR(55555.555, '99,999V99') | TO_CHAR(NULL, '999.999') |
+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
|   11,111.1110                      |  011,111.1110                      |  55,555.56                      |  55,55556                       |                     NULL |
+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
1 row in set (0.001 sec)

TO_CHAR(number)

Description

Syntax

Parameters

fmt

Numeric

Grouping

Currency

Conversion classes

Sign classes

Traditional classes

Other classes

Return type

Example

References

Contents

Description​

Syntax​

Parameters​

fmt​

Numeric​

Grouping​

Currency​

Conversion classes​

Sign classes​

Traditional classes​

Other classes​

Return type​

Example​

References​

Contents

Description

Syntax

Parameters

fmt

Numeric

Grouping

Currency

Conversion classes

Sign classes

Traditional classes

Other classes

Return type

Example

References