- 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:
exprAn expression of any data type.
numeric_exprA numeric expression.
date_or_time_exprAn expression of type DATE, TIME, or TIMESTAMP.
binary_exprAn expression of type BINARY or VARBINARY.
Optional:
formatThe format of the output string:
For
numeric_expr, specifies the SQL format model used tointerpret the numeric expression. For more information, seeSQL format models.For
date_or_time_expr, specifies the expected format to parseor produce a string. For more information, seeDate and time formats in conversion functions.The default is the current value of the following sessionparameters:
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 producethe string (e.g. ‘HEX’, ‘BASE64’ or ‘UTF-8’).For more information, seeOverview of supported binary formats.
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 containinga JSON document or JSON elementary value (unless VARIANT or OBJECTcontains an XML tag, in which case the output is a string containingan XML document):
A string stored in VARIANT is preserved as is (i.e. it is not converted toa JSON string).
A JSONnull 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 specifiedformats:
CREATEORREPLACETABLEconvert_numbers_to_strings(column1NUMBER);INSERTINTOconvert_numbers_to_stringsVALUES(-12.391),(0),(-1),(0.10),(0.01),(3987),(1.111);SELECTcolumn1ASorig_value,TO_CHAR(column1,'">"$99.0"<"')ASD2_1,TO_CHAR(column1,'">"B9,999.0"<"')ASD4_1,TO_CHAR(column1,'">"TME"<"')ASTME,TO_CHAR(column1,'">"TM9"<"')ASTM9,TO_CHAR(column1,'">"0XXX"<"')ASX4,TO_CHAR(column1,'">"S0XXX"<"')ASSX4FROMconvert_numbers_to_strings;
+------------+----------+------------+-------------+------------+--------+---------+| 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 easierto see where spaces are inserted.The
D2_1column shows the values with a$printed before the digits.For the
3987value, there are more digits in the integer part of the number than there are digit positionsin the format, so all digits are printed as#to indicate overflow.For the
0.10,0.01, and1.111values, there are more digits in the fractional part of the numberthan there are digit positions in the format, so the fractional values are truncated.
The
D4_1column shows that zero values are represented as spaces in the integer parts of thenumbers.For the
0,0.10, and0.01values, a space replaces the zero before the separator.For the
0.10,0.01, and1.111values, there are more digits in the fractional part ofthe number than there are digit positions in the format, so the fractional values are truncated.
The
TMEcolumn shows the values in scientific notation.The
TM9column shows the values as integers or decimal fractions, based on the value of the number.The
X4column shows the values as hexadecimal digits without the fractional parts.The
SX4column shows the values as hexadecimal digits of the absolute value of the numbers andincludes the numeric sign (+or-).
This example converts a logarithmic value to a string:
SELECTTO_VARCHAR(LOG(3,4));
+----------------------+| TO_VARCHAR(LOG(3,4)) ||----------------------|| 1.261859507 |+----------------------+
Examples that convert timestamps and dates¶
Convert a TIMESTAMP value to a string in the specified format:
SELECTTO_VARCHAR('2024-04-05 01:02:03'::TIMESTAMP,'mm/dd/yyyy, hh24:mi hours');
+---------------------------------------------------------------------------+| 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:
SELECTTO_VARCHAR('03-April-2024'::DATE);
+-----------------------------------+| TO_VARCHAR('03-APRIL-2024'::DATE) ||-----------------------------------|| 2024-04-03 |+-----------------------------------+
Convert a DATE value to a string in the specified format:
SELECTTO_VARCHAR('03-April-2024'::DATE,'yyyy.mm.dd');
+-------------------------------------------------+| TO_VARCHAR('03-APRIL-2024'::DATE, 'YYYY.MM.DD') ||-------------------------------------------------|| 2024.04.03 |+-------------------------------------------------+