to_number function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Returns expr cast to DECIMAL using formatting fmt.

Syntax 

to_number(expr, fmt)

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

Arguments

  • expr: A STRING expression representing a number. expr may include leading or trailing spaces.

  • fmt: A STRING literal, specifying the expected format of expr.

Returns

A DECIMAL(p, s) where p is the total number of digits (0 or 9) and s is the number of digits after the decimal point, or 0 if there is none.

fmt can contain the following elements (case insensitive):

  • 0 or 9

    Specifies an expected digit between 0 and 9. A 0 to the left of the decimal points indicates that expr must have at least as many digits. Leading 9 indicate that expr may omit these digits.

    expr must not be larger that the number of digits to the left of the decimal point allows.

    Digits to the right of the decimal indicate the most digits expr may have to the right of the decimal point than fmt specifies.

  • . or D

    Specifies the position of the decimal point.

    expr does not need to include a decimal point.

  • , 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. expr must match the grouping separator relevant to the size of the number.

  • L or $

    Specifies the location of the $ currency sign. This character may only be specified once.

  • S or MI

    Specifies the position of an optional ‘+’ or ‘-‘ sign for S, and ‘-‘ only for MI. This directive may be specified only once.

  • PR

    Only allowed at the end of the format string; specifies that expr indicates a negative number with wrapping angled brackets (<1>).

If expr contains any characters other than 0 through 9, or characters permitted in fmt, an error is returned.

To return NULL instead of an error for invalid expr use try_to_number().

Examples 

-- The format expects:
--  * an optional sign at the beginning,
--  * followed by a dollar sign,
--  * followed by a number between 3 and 6 digits long,
--  * thousands separators,
--  * up to two dight beyond the decimal point.
> SELECT to_number('-$12,345.67', 'S$999,099.99');
 -12345.67

-- Plus is optional, and so are fractional digits.
> SELECT to_number('$345', 'S$999,099.99');
 345.00

-- The format requires at least three digits.
> SELECT to_number('$45', 'S$999,099.99');
 Error: INVALID_FORMAT.MISMATCH_INPUT

-- The format requires at least three digits.
> SELECT try_to_number('$45', 'S$999,099.99');
 NULL

-- The format requires at least three digits
> SELECT to_number('$045', 'S$999,099.99');
 45.00

-- Using brackets to denote negative values
> SELECT to_number('<1234>', '999999PR');
 -1234