to_char
function
April 18, 2024
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above
Returns expr
cast to STRING
using formatting fmt
.
In Databricks Runtime 14.0 and earlier to_char
supports expr
of numeric types.
In Databricks SQL and Databricks Runtime 14.1 and above to_char
also supports expr
of types DATE
, TIMESTAMP
, and BINARY
to_char
is a synonym for to_varchar.
Syntax
to_char(expr, { numericFormat | datetimeFormat | stringFormat } )
numericFormat
{ ' [ S ] [ L | $ ]
[ 0 | 9 | G | , ] [...]
[ . | D ]
[ 0 | 9 ] [...]
[ L | $ ] [ PR | MI | S ] ' }
Arguments
expr
: An expression of type numeric, datetime,STRING
, orBINARY
.numericFormat
: ASTRING
literal, specifying the formatted output forexpr
of type numeric.datetimeFormat
: ASTRING
literal, specifying the formatted output forexpr
of type datetime.stringFormat
: ASTRING
literal, specifying the formatted output forexpr
of typeBINARY
.
Returns
A STRING
representing the result of the formatting operation.
datetmeFormat
can contain the patterns specified in Datetime patterns.
stringFormat
can be one of the following (case insensitive):
‘base64’
A base 64 string.
‘hex’
A string in the hexadecimal format.
‘utf-8’
The input binary is decoded to UTF-8 string.
numericFormat
can contain the following elements (case insensitive):
0
or9
Specifies an expected digit between 0 and 9. A sequence of digits with values between 0 and 9 in the format string matches a sequence of digits in the input value. This generates a result string of the same length as the corresponding sequence as the format string. The result string is left-padded with zeros if the 0/9 sequence comprises more digits than the matching part of the decimal value, starts with 0, and is before the decimal point. Otherwise, it is padded with spaces.
.
orD
Specifies the position of the decimal point (optional, only allowed once).
,
orG
Specifies the position of the grouping (thousands) separator (,). There must be a 0 or 9 to the left and right of each grouping separator.
$
Specifies the location of the $ currency sign. This character may only be specified once.
S
orMI
Specifies the position of a ‘-‘ or ‘+’ sign (optional, only allowed once at the beginning or end of the format string). Note that
S
prints+
for positive values butMI
prints a space.PR
Only allowed at the end of the format string; specifies that the result string will be wrapped by angle brackets if the input value is negative. (‘<1>’).
If either the integral or the decimal part in numExpr
requires more digits than the corresponding part allowed in fmt
, the function returns both the parts in the fmt
with 0
and 9
replaced by #
(e.g '$###.##'
).
If fmt
is malformed Databricks SQL returns an error.
This function is an alias for to_varchar.
Examples
> SELECT to_char(454, '999');
454
> SELECT to_char(454, '000.00');
454.00
> SELECT to_char(12454, '99,999');
12,454
> SELECT to_char(78.12, '$99.99');
$78.12
> SELECT to_char(-12454.8, '99,999.9S');
12,454.8-
> SELECT to_char(12454.8, '99,999.9S');
12,454.8+
> SELECT '>' || to_char(123, '00000.00') || '<';
>00123.00<
> SELECT '>' || to_char(123, '99999.99') || '<';
> 123.00<
> SELECT to_char(1.1, '99');
##
> SELECT to_char(111.11, '99.9');
##.#
> SELECT to_char(111.11, '$99.9');
$##.#
> SELECT to_char(date'2016-04-08', 'y');
2016
> SELECT to_char(x'537061726b2053514c', 'base64');
U3BhcmsgU1FM
> SELECT to_char(x'537061726b2053514c', 'hex');
537061726B2053514C
> SELECT to_char(encode('abc', 'utf-8'), 'utf-8');
abc
> SELECT to_char(111, 'wrong');
Error: 'wrong' is a malformed format.