[CALCITE-5978] Add REGEXP_INSTR function (enabled in BigQuery library)

• Type: Task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Labels: pull-request-available
• Assignee: Jerin John
• Reporter: Jerin John
• Created: 05/Sep/23 22:17
• Updated: 10/Nov/23 11:34
• Resolved: 15/Sep/23 17:50

Description

Add support for REGEXP_INSTR function from BigQuery.

REGEXP_INSTR(string, regexp [, position [, occurrence [, occurrence_position]]])
Returns the lowest 1-based position of the substring in string that matches the regexp, Returns 0 if there is no match

• If position is specified, the search starts at this position in string, otherwise it starts at the beginning of string.
• If occurrence is specified, the search returns index for the specific occurrence of the regexp in value, otherwise returns the first match.
• If occurrence_position is specified as 1, returns the end index of substring + 1 (default 0 returns start index of match)

Example:

SELECT REGEXP_INSTR("abcadcabcaecghi", "adc") as result;

result

4

Issue Links

• is a child of: CALCITE-5858 Add REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR functions (enabled in BigQuery library)
• links to: GitHub Pull Request #3408