EscaperLL opened a new issue, #70548:
URL: https://github.com/apache/doris/issues/70548
### Search before asking
Searched existing issues and PRs. This is related to #51351 and the
function-support tracking issue #48203. #51351 was closed by the stale bot; the
associated attempts #51462 and #55074 were closed without merging. This request
defines a broader interface with different semantics, rather than claiming to
implement Trino's interface unchanged.
### Description
Add a `regexp_position` scalar function and the `regexp_instr`,
`regexpPosition`, and `regexpInstr` aliases:
```sql
regexp_position(haystack, pattern[, position[, occurrence[, return_option[,
flags[, subexpression]]]]])
```
- Return a one-based **byte** position as `BIGINT`; return `0` when no match
or the requested capture group does not participate.
- Support selecting the non-overlapping occurrence and capture group.
`return_option=0` returns its start; `return_option=1` returns the position
immediately after its end.
- Default optional arguments to `1, 1, 0, '', 0` respectively. Pattern and
flags must be deterministic constants; haystack and numeric arguments may vary
per row.
- Support matching flags and RE2 patterns, propagate NULL per row, and
reject invalid arguments rather than returning a no-match result.
- Use the ClickHouse 26.10.2.103 baseline for matching and byte-offset
behavior, including its distinction between the unflagged empty literal pattern
and general zero-width matches. The specified Doris behavior gives row-level
NULL propagation precedence over validating that row's numeric values.
Examples:
```sql
SELECT regexp_position('abcabc', 'b'); -- 2
SELECT regexp_position('abcabc', 'b', 1, 2); -- 5
SELECT regexp_position('abcabc', '(b)', 1, 2, 1, '', 1); -- 6
SELECT regexp_position('abc', 'z'); -- 0
```
Unlike the original Trino-oriented #51351 request, this interface returns
`0` instead of `-1` on no match, uses byte offsets, and accepts up to seven
arguments. These compatibility choices should be reviewed explicitly.
### Use case
Find regex match and capture-group positions in SQL for parsing and
extracting portions of strings, including migration of queries using the
ClickHouse-style interface, without moving the matching logic into application
code.
### Related issues
- #51351: earlier Trino-oriented request, closed after inactivity.
- #48203: cross-system SQL function tracking issue.
- #51462 and #55074: earlier closed, unmerged implementation attempts.
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
An implementation with FE/BE unit tests and SQL regression coverage is ready
for review.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
