[
https://issues.apache.org/jira/browse/SPARK-38796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wenchen Fan reassigned SPARK-38796:
-----------------------------------
Assignee: Daniel
> Implement the to_number and try_to_number SQL functions according to a new
> specification
> ----------------------------------------------------------------------------------------
>
> Key: SPARK-38796
> URL: https://issues.apache.org/jira/browse/SPARK-38796
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 3.4.0
> Reporter: Daniel
> Assignee: Daniel
> Priority: Major
>
> This tracks implementing the 'to_number' and 'try_to_number' SQL function
> expressions according to new semantics described below. The former is
> equivalent to the latter except that it throws an exception instead of
> returning NULL for cases where the input string does not match the format
> string.
>
> -----------
>
> *try_to_number function (expr, fmt):*
> Returns 'expr' cast to DECIMAL using formatting 'fmt', or 'NULL' if 'expr' is
> not a valid match for the given format.
>
> Syntax:
> [ 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': An 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.
>
> *Format elements allowed (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. A leading '9' indicates that 'expr' may omit these
> digits.
> 'expr' must not be larger than the number of digits to the left of the
> decimal point allowed by the format string.
> Digits to the right of the decimal point in the format string indicate the
> most digits that 'expr' may have to the right of the decimal point.
> * . 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 of the rightmost grouping separator.
> 'expr' must match the grouping separator relevant for the size of the
> number.
> * $
> Specifies the location of the '$' currency sign. This character may only be
> specified once.
> * S
> Specifies the position of an option '+' or '-' sign. This character may
> only be specified once.
> * MI
> Specifies that 'expr' has an optional '-' sign at the end, but no '+'.
> * PR
> Specifies that 'expr' indicates a negative number with wrapping angled
> brackets ('<1>'). If 'expr' contains any characters other than '0' through
> '9' and those permitted in 'fmt' a 'NULL' is returned.
>
> *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 dights beyond the decimal point. }}
> {{> SELECT try_to_number('-$12,345.67', 'S$999,099.99');}}
> {{ -12345.67}}
> {{– The plus sign 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 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}}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
