beliefer opened a new pull request #35060: URL: https://github.com/apache/spark/pull/35060
### What changes were proposed in this pull request? Many database support the function `to_number` to convert a string to number. The implement of `to_number` has many different between `Postgresql` ,`Oracle` and `Phoenix`. So, this PR follows the implement of `to_number` in `Oracle` that give a strict parameter verification. So, this PR follows the implement of `to_number` in `Phoenix` that uses BigDecimal. This PR support the patterns for numeric formatting as follows: Pattern | Description -- | -- 9 | Value with the specified number of digits 0 | Value with leading zeros . (period) | Decimal point , (comma) | Group (thousand) separator S | Sign anchored to number (uses locale) $ | a value with a leading dollar sign D | Decimal point (uses locale) G | Group separator (uses locale) There are some mainstream database support the syntax. **PostgreSQL:** https://www.postgresql.org/docs/12/functions-formatting.html **Oracle:** https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_NUMBER.html#GUID-D4807212-AFD7-48A7-9AED-BEC3E8809866 **Vertica** https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_NUMBER.htm?tocpath=SQL%20Reference%20Manual%7CSQL%20Functions%7CFormatting%20Functions%7C_____7 **Redshift** https://docs.aws.amazon.com/redshift/latest/dg/r_TO_NUMBER.html **DB2** https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1544.htm **Teradata** https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/TH2cDXBn6tala29S536nqg **Snowflake:** https://docs.snowflake.net/manuals/sql-reference/functions/to_decimal.html **Exasol** https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/to_number.htm#TO_NUMBER **Phoenix** http://phoenix.incubator.apache.org/language/functions.html#to_number **Singlestore** https://docs.singlestore.com/v7.3/reference/sql-reference/numeric-functions/to-number/ **Intersystems** https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_TONUMBER The syntax like: > select to_number('12,454.8-', '99G999D9S'); -12454.8 ### Why are the changes needed? `to_number` is very useful for formatted currency to number conversion. ### Does this PR introduce any user-facing change? Yes. New feature. ### How was this patch tested? New tests -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
