[CALCITE-3959] Implement INSTR function

• Type: Wish
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Joey Moore
• Reporter: xzh_dz
• Created: 25/Apr/20 07:57
• Updated: 27/Jul/23 10:22
• Resolved: 04/May/23 17:15

Description

BiqQuery and Oracle both support functionally identical INSTR(source_value, search_value[, position[, occurrence]]) functions which accepts 2 (character strings or binary strings), 1 optional int representing position, and 1 optional int representing occurrence.

Occurrence and position are assigned a default value of 1 if not specified.

The function returns the 1-based position of the nth occurrence of the 2nd operand in the 1st operand where n is defined by the 4th operand. The function begins searching at the 1-based position specified in the 3rd operand.

The function also supports negative position values, with -1 indicating the last character, and will search backwards from the position specified in that case. 

Returns 0 if:

• No match is found.
• If occurrence is greater than the number of matches found.
• If position is greater than the length of source_value.

Returns NULL if:

• Any input argument is NULL.

Returns an error if:

• position is 0.
• occurrence is 0 or negative.

EXAMPLE: INSTR("abc", "bc") would return 2.

EXAMPLE: INSTR("abcabc", "bc", 3) would return 5.

EXAMPLE: INSTR("abcabc", "bc", -1, 1) would return 5.

EXAMPLE: INSTR("abcabc", "bc", -1, 2) would return 2.

 

MySQL also has an  INSTR function, the functionality of which is a subset of the INSTR present in BQ and Oracle. MySQL INSTR only takes 2 parameters and returns the first occurrence of the search value in the source value. 

Issue Links

• links to: GitHub Pull Request #3184