to_char function

Applies to: check marked yes Databricks SQL check marked yes 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, or BINARY.

  • numericFormat: A STRING literal, specifying the formatted output for expr of type numeric.

  • datetimeFormat: A STRING literal, specifying the formatted output for expr of type datetime.

  • stringFormat: A STRING literal, specifying the formatted output for expr of type BINARY.

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 or 9

    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.

  • . or D

    Specifies the position of the decimal point (optional, only allowed once).

  • , or G

    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 or MI

    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 but MI 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.