[FLINK-36025] Add TO_NUMBER function

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Table SQL / API
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Dylan He
• Created: 09/Aug/24 09:59
• Updated: 21/Aug/24 09:24

Description

Add TO_NUMBER function.

---

Returns expr cast to DECIMAL using formatting format.

Example:

-- 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');
 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

Syntax:

TO_NUMBER(expr, format)

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

Arguments:

• expr: A STRING expression representing a number. expr may include leading or trailing spaces.
• format: 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.
NULL if format is invalid or expr mismatches format.

---

format 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 format 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>).

See also:

• Spark
• Databricks
• Snowflake
• PostgreSql
• Oracle

Issue Links

• links to: GitHub Pull Request #25216