to_char function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.1 and above

Returns numExpr cast to STRING using formatting fmt.

Syntax 

to_char(numExpr, fmt)

fmt
  { ' [ S ] [ L | $ ]
      [ 0 | 9 | G | , ] [...]
      [ . | D ]
      [ 0 | 9 ] [...]
      [ L | $ ] [ PR | MI | S ] ' }

Arguments

  • numExpr: A numeric expression.

  • fmt: An STRING literal, specifying the formatted output for numExpr.

Returns

A STRING representing the result of the formatting operation.

fmt 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 numExpr requires more digits than fmt allows for, the function returns the fmt with 0 and 9 replaced by # (e.g '$###.##').

If fmt is malformed Databricks SQL returns an error.

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(111, 'wrong');
 Error: 'wrong' is a malformed format.