[jira] [Resolved] (CALCITE-3959) Implement INSTR function

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-3959.
----------------------------------
    Fix Version/s: 1.35.0
       Resolution: Fixed

Fixed in [9ea86247|https://github.com/apache/calcite/commit/9ea862478d88b6163e47c57521e222ea5e582e47]; thanks for the PR, [~jhugomoore]!

> 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
>              Labels: pull-request-available
>             Fix For: 1.35.0
>
>
> [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)