Share via

try_to_number function

  • Article

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, or NULL if expr does not match the format.

Syntax

try_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 are no digits after the decimal point.

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 maximum number of digits expr may have to the right of the decimal point specified by fmt.

  • . 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

    Specifies that expr indicates a negative number with wrapping angled brackets (<1>).

If expr contains any characters other than 0 through 9, or those permitted in fmt, a NULL is returned.

For strict semantic use 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 try_to_number('-$12,345.67', 'S$999,099.99');
 -12345.67

-- Plus is optional, and so are fractional digits.
> SELECT try_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 number

-- 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 try_to_number('$045', 'S$999,099.99');
 45.00

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