TO_CHAR(number)

Purpose

This function converts numeric data to VARCHAR data.

Syntax

TO_CHAR(number [, fmt])

Parameters

• number: the numeric value to convert.
• fmt: an optional parameter that specifies the numeric output format. If you omit fmt, 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: EEEE , RN , V , and X.
• Sign: MI , PR , and S.
• Traditional: TM.
• Other: B and FM.

Numeric

• 0: A placeholder indicating a numeric position. If a character is present at the corresponding position, it is returned; otherwise, it is filled with 0.

  Notice

  0 is a mandatory symbol. If no character is present at the corresponding position, it is filled with 0. This is different from 9.

  Example:

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

  The result is as follows:

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

• 9: A placeholder indicating a numeric position. The specific rules are as follows:

  • If it is in the decimal part, it indicates that the corresponding character should be converted. If no character is present, it is filled with 0.

  • If it is in the integer part, no character is filled.

    Example:

    SELECT TO_CHAR(123, '999999'), TO_CHAR(123.456, '9999.9999');
    

    The result is as follows:

    +------------------------+-------------------------------+
    | TO_CHAR(123, '999999') | TO_CHAR(123.456, '9999.9999') |
    +------------------------+-------------------------------+
    |     123                |   123.4560                    |
    +------------------------+-------------------------------+
    1 row in set
    

Grouping

• . (period): Indicates a decimal point at the specified position. If the decimal part is insufficient, it is filled with zeros. If the decimal part exceeds the number of digits specified by fmt, it is rounded.

  Notice

  fmt can only specify one decimal point.

  Example:

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

  The result is as follows:

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

• , (comma): Generally appears as a thousand separator and serves as a grouping symbol. It can also appear as a tenths or hundredths separator, depending on the size of the number.

  Notice

  , cannot appear at the beginning of fmt and can only appear in the integer part.

  Example:

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

  The 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
  

• D (ISO decimal separator): The international version of the period (ISO) with the same function as .. It can only appear once. The difference is that it uses the default value of the NLS_NUMERIC_CHARACTER parameter.

  Notice

  Unless necessary, 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 result is as follows:

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

• G (ISO grouping separator): The ISO standard for the comma, serving as a grouping symbol. It can appear in multiple places.

  Notice

  If you want to convert the 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 result is as follows:

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

Currency

• $ (dollar sign): Returns the value with a leading dollar sign.

  Notice

  $ can appear anywhere in fmt, but only once.

  Example:

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

  The 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
  

• C (international currency symbol): Returns the ISO currency symbol (the value represented by the NLS_ISO_CURRENCY parameter) at the specified position.

  Notice

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

  Example:

  SELECT TO_CHAR(123.45, 'C999.99');
  

  The result is as follows:

  +----------------------------+
  | TO_CHAR(123.45, 'C999.99') |
  +----------------------------+
  |      USD123.45             |
  +----------------------------+
  1 row in set
  

• L (local currency symbol): The local version of C. Returns the local currency symbol at the specified position. It can appear at the beginning and end of the entire format.

  Example:

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

  The result is as follows:

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

• U: Returns the dual currency symbol (determined by the value of the NLS_DUAL_CURRENCY parameter) at the specified position.

  Example:

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

  The 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
  

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 as follows:

  +-------------------------+---------------------------+-----------------------------+
  | 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
  

• RN (rn): converts an integer (1 to 3999) to Roman numerals. RN represents uppercase, and rn represents lowercase.

  Notice

  RN ( rn ) cannot be combined with other symbols.

  Example:

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

  The result is as follows:

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

• V: returns a value multiplied by 10^n (rounded as necessary), where n is the number of 9s following the V.

  Notice

  V cannot be used with decimal expressions in the fmt format, but 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 as follows:

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

• 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. Limitations:

  • This element accepts only positive values or 0. Negative values return an error.

  • You can add only 0 (to return leading zeros) or FM before this element. Any other value returns an error. If you use X without specifying 0 or FM, the result always has a leading space.

    Example:

    SELECT TO_CHAR(10, 'X'), TO_CHAR(10, 'XXXX');
    

    The result is as follows:

    +------------------+---------------------+
    | TO_CHAR(10, 'X') | TO_CHAR(10, 'XXXX') |
    +------------------+---------------------+
    |  A               |     A               |
    +------------------+---------------------+
    1 row in set
    

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.

  Notice

  MI can appear only at the end of a numeric format model.

  Example:

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

  The result is as follows:

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

• PR: another way to express negative numbers:

  • If the value is positive, a space is added at the beginning.

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

    Notice

    PR can appear only at the end of a numeric format model.

    Example:

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

    The result is as follows:

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

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

  Notice

  S can appear only at the beginning or end of a numeric format model.

  Example:

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

  The result is as follows:

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

Standard classes

TM: the default value is TM9. If the output does not exceed 64 characters, it returns the number in fixed notation. If the output exceeds 64 characters, it automatically returns the number in scientific notation. When the number of digits exceeds 64, the output of TM9 is the same as that of TME.

Example:

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

The result is as follows:

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

Other classes

• B (space): adds a space at the beginning of the integer part. It can appear anywhere.

  Notice

  B can appear only in the integer part.

  Example:

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

  The result is as follows:

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

• FM (fm): removes leading spaces.

  Notice

  FM 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 as follows:

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

Return type

• Returns data of the VARCHAR type.
• Returns NULL when the number value is NULL.

Examples

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 return 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

References

• TO_CHAR(character)
• TO_CHAR(datetime)