Dylan He created FLINK-36025:
--------------------------------
Summary: Add TO_NUMBER function
Key: FLINK-36025
URL: https://issues.apache.org/jira/browse/FLINK-36025
Project: Flink
Issue Type: Sub-task
Components: Table SQL / API
Reporter: Dylan He
Add TO_NUMBER function.
----
Returns {{expr}} cast to DECIMAL using formatting {{fmt}}.
Example:
{code:sql}
-- 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 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 to_number('$045', 'S$999,099.99');
45.00
-- Using brackets to denote negative values
> SELECT to_number('<1234>', '999999PR');
-1234
{code}
Syntax:
{code:sql}
TO_NUMBER(expr, fmt)
fmt
{ ' [ MI | S ] [ L | $ ]
[ 0 | 9 | G | , ] [...]
[ . | D ]
[ 0 | 9 ] [...]
[ L | $ ] [ PR | MI | S ] ' }
{code}
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.
See also:
*
[Spark|https://spark.apache.org/docs/3.5.1/sql-ref-functions-builtin.html#string-functions]
*
[Databricks|https://docs.databricks.com/en/sql/language-manual/functions/to_number.html]
* [Snowflake|https://docs.snowflake.com/en/sql-reference/functions/to_decimal]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
