[HIVE-25230] add position and occurrence to instr()

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-2
• Component/s: UDF
• Labels: pull-request-available
• Assignee: Quanlong Huang
• Reporter: Quanlong Huang
• Created: 10/Jun/21 02:28
• Updated: 16/Nov/22 13:50
• Resolved: 13/May/22 01:24

Description

Current instr() only supports two arguments:

instr(str, substr) - Returns the index of the first occurance of substr in str

Other systems (Vertica, Oracle, Impala etc) support additional position and occurrence arguments:

instr(str, substr[, pos[, occurrence]])

Oracle doc: https://docs.oracle.com/database/121/SQLRF/functions089.htm#SQLRF00651

It'd be nice to support this as well. Otherwise, it's a SQL difference between Impala and Hive.
Impala supports this in IMPALA-3973

Issue Links

• links to: GitHub Pull Request #2378