Categories:

Conversion Functions

TO_CHAR , TO_VARCHAR

Converts the input expression to a string. For NULL input, the output is NULL.

These functions are synonymous.

Syntax

TO_CHAR( <expr> )
TO_CHAR( <numeric_expr> [, '<format>' ] )
TO_CHAR( <date_or_time_expr> [, '<format>' ] )
TO_CHAR( <binary_expr> [, '<format>' ] )

TO_VARCHAR( <expr> )
TO_VARCHAR( <numeric_expr> [, '<format>' ] )
TO_VARCHAR( <date_or_time_expr> [, '<format>' ] )
TO_VARCHAR( <binary_expr> [, '<format>' ] )
Copy

Arguments

Required:

expr

An expression of any data type.

numeric_expr

A numeric expression.

date_or_time_expr

An expression of type DATE, TIME, or TIMESTAMP.

binary_expr

An expression of type BINARY or VARBINARY.

Optional:

format

The format of the output string:

Returns

This function returns a value of VARCHAR data type or NULL.

Usage notes

  • For VARIANT, ARRAY, or OBJECT inputs, the output is the string containing a JSON document or JSON elementary value (unless VARIANT or OBJECT contains an XML tag, in which case the output is a string containing an XML document):

    • A string stored in VARIANT is preserved as is (i.e. it is not converted to a JSON string).

    • A JSON null value is converted to a string containing the word “null”.

Examples

The following examples convert numbers, timestamps, and dates to strings.

Examples that convert numbers

Convert numeric values to strings in the specified formats:

CREATE OR REPLACE TABLE convert_numbers_to_strings(column1 NUMBER);

INSERT INTO convert_numbers_to_strings VALUES
  (-12.391),
  (0),
  (-1),
  (0.10),
  (0.01),
  (3987),
  (1.111);

SELECT column1 AS orig_value,
       TO_CHAR(column1, '">"$99.0"<"') AS D2_1,
       TO_CHAR(column1, '">"B9,999.0"<"') AS D4_1,
       TO_CHAR(column1, '">"TME"<"') AS TME,
       TO_CHAR(column1, '">"TM9"<"') AS TM9,
       TO_CHAR(column1, '">"0XXX"<"') AS X4,
       TO_CHAR(column1, '">"S0XXX"<"') AS SX4
  FROM convert_numbers_to_strings;
Copy
+------------+----------+------------+-------------+------------+--------+---------+
| ORIG_VALUE | D2_1     | D4_1       | TME         | TM9        | X4     | SX4     |
|------------+----------+------------+-------------+------------+--------+---------|
|    -12.391 | >-$12.4< | >   -12.4< | >-1.2391E1< | >-12.391<  | >FFF4< | >-000C< |
|      0.000 | >  $0.0< | >      .0< | >0E0<       | >0.000<    | >0000< | >+0000< |
|     -1.000 | > -$1.0< | >    -1.0< | >-1E0<      | >-1.000<   | >FFFF< | >-0001< |
|      0.100 | >  $0.1< | >      .1< | >1E-1<      | >0.100<    | >0000< | >+0000< |
|      0.010 | >  $0.0< | >      .0< | >1E-2<      | >0.010<    | >0000< | >+0000< |
|   3987.000 | > $##.#< | > 3,987.0< | >3.987E3<   | >3987.000< | >0F93< | >+0F93< |
|      1.111 | >  $1.1< | >     1.1< | >1.111E0<   | >1.111<    | >0001< | >+0001< |
+------------+----------+------------+-------------+------------+--------+---------+

The output illustrates how the values are converted to strings based on the specified formats:

  • The > and < symbols are string literals that are included in the output. They make it easier to see where spaces are inserted.

  • The D2_1 column shows the values with a $ printed before the digits.

    • For the 3987 value, there are more digits in the integer part of the number than there are digit positions in the format, so all digits are printed as # to indicate overflow.

    • For the 0.10, 0.01, and 1.111 values, there are more digits in the fractional part of the number than there are digit positions in the format, so the fractional values are truncated.

  • The D4_1 column shows that zero values are represented as spaces in the integer parts of the numbers.

    • For the 0, 0.10, and 0.01 values, a space replaces the zero before the separator.

    • For the 0.10, 0.01, and 1.111 values, there are more digits in the fractional part of the number than there are digit positions in the format, so the fractional values are truncated.

  • The TME column shows the values in scientific notation.

  • The TM9 column shows the values as integers or decimal fractions, based on the value of the number.

  • The X4 column shows the values as hexadecimal digits without the fractional parts.

  • The SX4 column shows the values as hexadecimal digits of the absolute value of the numbers and includes the numeric sign (+ or -).

This example converts a logarithmic value to a string:

SELECT TO_VARCHAR(LOG(3,4));
Copy
+----------------------+
| TO_VARCHAR(LOG(3,4)) |
|----------------------|
| 1.261859507          |
+----------------------+

Examples that convert timestamps and dates

Convert a TIMESTAMP value to a string in the specified format:

SELECT TO_VARCHAR('2024-04-05 01:02:03'::TIMESTAMP, 'mm/dd/yyyy, hh24:mi hours');
Copy
+---------------------------------------------------------------------------+
| TO_VARCHAR('2024-04-05 01:02:03'::TIMESTAMP, 'MM/DD/YYYY, HH24:MI HOURS') |
|---------------------------------------------------------------------------|
| 04/05/2024, 01:02 hours                                                   |
+---------------------------------------------------------------------------+

Convert a DATE value to a string in the default format:

SELECT TO_VARCHAR('03-April-2024'::DATE);
Copy
+-----------------------------------+
| TO_VARCHAR('03-APRIL-2024'::DATE) |
|-----------------------------------|
| 2024-04-03                        |
+-----------------------------------+

Convert a DATE value to a string in the specified format:

SELECT TO_VARCHAR('03-April-2024'::DATE, 'yyyy.mm.dd');
Copy
+-------------------------------------------------+
| TO_VARCHAR('03-APRIL-2024'::DATE, 'YYYY.MM.DD') |
|-------------------------------------------------|
| 2024.04.03                                      |
+-------------------------------------------------+
Language: English