[SPARK-28325] Support ANSI SQL:SIMILAR TO ... ESCAPE syntax

• Type: Sub-task
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Jiaan Geng
• Created: 10/Jul/19 07:29
• Updated: 30/Nov/20 08:38

Description

<similar predicate> ::=
<row value predicand> <similar predicate part 2>
<similar predicate part 2> ::=
[ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]
<similar pattern> ::=
<character value expression>
<regular expression> ::=
<regular term>
| <regular expression> <vertical bar> <regular term>
<regular term> ::=
<regular factor>
| <regular term> <regular factor>
<regular factor> ::=
<regular primary>
| <regular primary> <asterisk>
| <regular primary> <plus sign>
| <regular primary> <question mark>
| <regular primary> <repeat factor>
<repeat factor> ::=
<left brace> <low value> [ <upper limit> ] <right brace>
<upper limit> ::=
<comma> [ <high value> ]
<low value> ::=
<unsigned integer>
<high value> ::=
<unsigned integer>
<regular primary> ::=
<character specifier>
| <percent>
| <regular character set>
| <left paren> <regular expression> <right paren>
<character specifier> ::=
<non-escaped character>
| <escaped character>
<non-escaped character> ::=
!! See the Syntax Rules.
494 Foundation (SQL/Foundation)
CD 9075-2:201?(E)
8.6 <similar predicate>
<escaped character> ::=
!! See the Syntax Rules.
<regular character set> ::=
<underscore>
| <left bracket> <character enumeration>... <right bracket>
| <left bracket> <circumflex> <character enumeration>... <right bracket>
| <left bracket> <character enumeration include>...
<circumflex> <character enumeration exclude>... <right bracket>
<character enumeration include> ::=
<character enumeration>
<character enumeration exclude> ::=
<character enumeration>
<character enumeration> ::=
<character specifier>
| <character specifier> <minus sign> <character specifier>
| <left bracket> <colon> <regular character set identifier> <colon> <right bracket>
<regular character set identifier> ::=
<identifier>

 

 Examples:

SELECT 'abc' RLIKE '%(b|d)%';      // false
SELECT 'abc' SIMILAR TO '%(b|d)%'   // true
SELECT 'abc' RLIKE '(b|c)%';          // false
SELECT 'abc' SIMILAR TO '(b|c)%';     // false

 

Currently, the following DBMSs support the syntax:

• PostgreSQL:https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP
• Redshift: https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions-similar-to.html

Issue Links

• links to: GitHub Pull Request #27007
• links to: GitHub Pull Request #27432