[
https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Joey Moore updated CALCITE-3959:
--------------------------------
Description:
[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.
was:
BiqQuery and Oracle both support functionally identical
[INSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#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.
> Implement 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|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 message was sent by Atlassian Jira
(v8.20.10#820010)
