- Categories:
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>' ] )
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:
For
numeric_expr
, specifies the SQL format model used to interpret the numeric expression. For more information, see SQL Format Models.For
date_or_time_expr
, specifies the expected format to parse or produce a string. For more information, see Date and Time Formats in Conversion Functions.The default is the current value of the following session parameters:
DATE_OUTPUT_FORMAT (for DATE inputs)
TIME_OUTPUT_FORMAT (for TIME inputs)
TIMESTAMP_OUTPUT_FORMAT (for TIMESTAMP inputs)
For
binary_expr
, specifies the format in which to produce the string (e.g. ‘HEX’, ‘BASE64’ or ‘UTF-8’).For more information, see Overview of Supported Binary Formats.
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¶
select to_varchar('2013-04-05 01:02:03'::timestamp, 'mm/dd/yyyy, hh24:mi hours');
+---------------------------------------------------------------------------+
| TO_VARCHAR('2013-04-05 01:02:03'::TIMESTAMP, 'MM/DD/YYYY, HH24:MI HOURS') |
|---------------------------------------------------------------------------|
| 04/05/2013, 01:02 hours |
+---------------------------------------------------------------------------+
select to_varchar('03-May-2013'::date);
+---------------------------------+
| TO_VARCHAR('03-MAY-2013'::DATE) |
|---------------------------------|
| 2013-05-03 |
+---------------------------------+
select to_varchar('03-May-2013'::date, 'yyyy.mm.dd');
+-----------------------------------------------+
| TO_VARCHAR('03-MAY-2013'::DATE, 'YYYY.MM.DD') |
|-----------------------------------------------|
| 2013.05.03 |
+-----------------------------------------------+
select to_varchar(log(3,4));
+----------------------+
| TO_VARCHAR(LOG(3,4)) |
|----------------------|
| 1.261859507 |
+----------------------+
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 values (-12.391), (0), (-1), (0.10), (0.01), (3987), (1.111);
+------------+----------+------------+-------------+------------+--------+---------+
| 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< |
+------------+----------+------------+-------------+------------+--------+---------+