[SPARK-27949] Support SUBSTRING(str FROM n1 [FOR n2]) syntax

• Type: Sub-task
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: Zhu, Lipeng
• Reporter: Zhu, Lipeng
• Created: 05/Jun/19 01:47
• Updated: 29/Dec/19 07:00
• Resolved: 10/Jun/19 16:05

Description

Currently, function `substr/substring`'s usage is like `substring(string_expression, n1 [,n2])`.

But, the ANSI SQL defined the pattern for substr/substring is like `SUBSTRING(str FROM n1 [FOR n2])`. This gap makes some inconvenient when we switch to the SparkSQL.

• ANSI SQL-92: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Below are the mainly DB engines to support the ANSI standard for substring.

• PostgreSQL https://www.postgresql.org/docs/9.1/functions-string.html
• MySQL https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring
• Redshift https://docs.aws.amazon.com/redshift/latest/dg/r_SUBSTRING.html
• Teradata https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/XnePye0Cwexw6Pny_qnxVA

Issue Links

• links to: GitHub Pull Request #24802