[
https://issues.apache.org/jira/browse/FLINK-36025?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dylan He updated FLINK-36025:
-----------------------------
Description:
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]
* [PostgreSql|https://www.postgresql.org/docs/12/functions-formatting.html]
*
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html]
was:
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]
> 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
> Priority: Major
>
> 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]
> * [PostgreSql|https://www.postgresql.org/docs/12/functions-formatting.html]
> *
> [Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
