TO_CHAR(number)

Purpose

This function converts numeric data to VARCHAR data.

Syntax

TO_CHAR(number [, fmt])

Parameters

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

Numeric types

• 0: A placeholder that represents a numeric position. It returns the corresponding character at the specified position or fills the position with 0 if no character is present.

  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 that represents a numeric position. The following applies:

  • If the position is a decimal place, it returns the corresponding character or fills the position with 0 if no character is present.

  • If the position is an integer place, it does not fill the position with any character if no character is present.

    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 types

• . (period): Represents 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 in fmt, it is rounded.

  Notice

  fmt can only contain 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): Usually appears as a thousand separator and serves as a grouping symbol. It can also appear as a decimal separator and can be used multiple times 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 (.) as defined by ISO. It functions the same as . but 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 (,) as a grouping symbol. It can be used in multiple places.

  Notice

  If you want to convert a decimal point, you must use it with D, not 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 types

• $ (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 integer position 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, returning the local currency symbol at the specified position. 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 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. Since it's a scientific notation method, you can add a 9 or 0 before the decimal point, and multiple 9s are not meaningful.

  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 a Roman numeral. 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 if necessary), where n is the number of 9s following V.

  Notice

  V cannot be used with decimal expressions in fmt, but can be used with other symbols like currency.

  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. Restrictions:

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

  • You can only add 0 (to return leading zeros) or FM before this element. Any other element returns an error. If you use X without specifying 0 or FM, the result will always have 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 only appear 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 only appear 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 only appear 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 is less than or equal to 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 has more than 64 digits, TM9 returns the same result as 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 only appear 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 only appear as the first character in the integer part of fmt.

  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 result set 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)