[jira] [Comment Edited] (CALCITE-5585) Add STRPOS() Function (Enabled for BigQuery)

["Tanner Clary (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17701749#comment-17701749 ] 

Tanner Clary edited comment on CALCITE-5585 at 3/17/23 1:36 PM:
----------------------------------------------------------------

[~julianhyde] I updated my PR. I was able to use the {{POSITION}} implementation but I still think it can be improved. You'll see that I added {{PositionImplementor}} which is necessary because the operands for the functions are in opposite order, and the functions also handle binary strings differently. To account for this, I added {{STRPOS}} as a {{BuiltInMethod}} which first converts the binary strings to strings, and then passes them to the existing {{position()}} functions in {{SqlFunctions}}. 

An issue with this is that I now have {{strpos(string, string)}} in {{SqlFunctions}} which is basically a duplicate of {{position(string, string)}}. I was wondering how I could avoid this while also still correctly handling binary strings for {{STRPOS}}.

I also rebased on top of your julianhyde/stage work to avoid merge conflicts in {{RexImpTable}}


was (Author: JIRAUSER298151):

[~julianhyde] I updated my PR. I was able to use the {{POSITION}} implementation but I still think it can be improved. You'll see that I added {{PositionImplementor}} which is necessary because the operands for the functions are in opposite order, and the functions also handle binary strings differently. To account for this, I added {{STRPOS}} as a {BuiltInMethod}} which first converts the binary strings to strings, and then passes them to the existing {{position()}} functions in {{SqlFunctions}}. 

An issue with this is that I now have {{strpos(string, string)}} in {{SqlFunctions}} which is basically a duplicate of {{position(string, string)}}. I was wondering how I could avoid this while also still correctly handling binary strings for {{STRPOS}}.

I also rebased on top of your julianhyde/stage work to avoid merge conflicts in {{RexImpTable}}

> Add STRPOS() Function (Enabled for BigQuery)
> --------------------------------------------
>
>                 Key: CALCITE-5585
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5585
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Tanner Clary
>            Assignee: Tanner Clary
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 40m
>  Remaining Estimate: 0h
>
> BigQuery supports the {{STRPOS()}} function with accepts 2 {{character strings}} as arguments. The function returns the 1-based position of the first occurrence of the second string within the first (example below). If no occurrence is found, the function returns 0. The function also supports {{binary strings}} with the same behavior. Note that {{STRPOS}} differs from the standard {{POSITION}} function in that it counts the position of the character in a {{binary string}}, not the position of a byte.
> EXAMPLE: {{STRPOS("abc", "bc")}} would return: {{2}}.
> EXAMPLE: {{STRPOS("abc", "d")}} would return: {{0}}.
> [BigQuery docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#strpos]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)