TO_CHAR(number)

Purpose

This function converts numeric data to VARCHAR data.

Syntax

TO_CHAR(number [, fmt])

Parameters

• number: the numeric data to be converted.
• fmt: optional. The parameter for specifying 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

• 0: A placeholder indicating a numeric position. It returns the corresponding character at the specified position. If no character is available, it returns 0.

  Notice

  0 is a mandatory symbol. If no character is available at the corresponding position, it returns 0. This is different from 9.

  Here is an 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 the position is in the decimal part, it returns the corresponding character. If no character is available, it returns 0.

  • If the position is in the integer part, it does not return any character if no corresponding character is available.

    Here is an 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 padded with zeros. If the decimal part exceeds the number of digits specified in fmt, it is rounded.

  Notice

  fmt can contain only one decimal point.

  Here is an 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 tenths or hundredths separator and can appear 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.

  Here is an 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 symbol): The international version of the period (ISO) with the same function as .. It can appear only once. The difference is that it uses the default value of the NLS_NUMERIC_CHARACTER parameter.

  Notice

  Do not use this format symbol unless necessary. Do not change the value of the NLS_NUMERIC_CHARACTER parameter.

  Here is an 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: The ISO standard for the comma, serving as a grouping symbol. It can appear multiple times.

  Notice

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

  Here is an 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

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

  Notice

  $ can appear anywhere in fmt, but only once.

  Here is an 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 current NLS_ISO_CURRENCY parameter) at the specified position.

  Notice

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

  Here is an 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 appear at the beginning and end of the entire format.

  Here is an 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 (euro or other) determined by the current value of the NLS_DUAL_CURRENCY parameter at the specified position.

  Here is an 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. A 9 or 0 is added 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 from 1 to 3999 to Roman numerals. RN converts to uppercase, and rn converts to 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 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 a 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 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 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 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
  

Traditional 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 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 at any position.

  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 at the beginning of the integer part in 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)