jhugomoore opened a new pull request, #3184: URL: https://github.com/apache/calcite/pull/3184
[BiqQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr) and [Oracle](https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions058.htm) 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](https://www.w3schools.com/sql/func_mysql_instr.asp) 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. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
