[ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17709511#comment-17709511 ]
Julian Hyde commented on CALCITE-3959: -------------------------------------- What libraries? You mention BigQuery but a quick search shows MySQL and Oracle. The cleanest implementation might be to allow POSITION to take 4 arguments internally and desugar INSTR to POSITION. > Implement BigQuery INSTR function > --------------------------------- > > Key: CALCITE-3959 > URL: https://issues.apache.org/jira/browse/CALCITE-3959 > Project: Calcite > Issue Type: Wish > Reporter: xzh_dz > Assignee: Joey Moore > Priority: Major > > BiqQuery supports the > [INSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr](source_value, > search_value[, position[, occurrence]]) function 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. -- This message was sent by Atlassian Jira (v8.20.10#820010)