Return the 1-based character position of a regex match within the input string.
REGEXP_INSTR(str, pattern [, start [, occurrence [, return_opt [, flags]]]])## Overview Searches the input string for the regular expression and returns the 1-based character position of the requested match occurrence. With return_opt = 0 (the default), the returned position is the first character of the match; with return_opt = 1, it is the position immediately after the last character of the match. If no match is found, REGEXP_INSTR returns 0. This function is the regex-enabled equivalent of INSTR/STRPOS/POSITION. Use it when the thing you want to locate is described by a pattern rather than a fixed substring. ## Behavior - Returns NULL when the input string or pattern is NULL. - Returns 0 when no match is found or when the requested occurrence does not exist. - Positions are 1-based and count Unicode code points. - The start argument constrains where scanning begins; occurrences before `start` are ignored. - Occurrence is 1-based; 1 is the first match after `start`. - return_opt = 0 (start of match) and return_opt = 1 (position after match) are the only supported values. ## Regex flavor - Rust regex crate, Perl-compatible for most common features. - Supports anchors `^` and `$`, character classes, alternation, quantifiers, capturing groups, Unicode classes via `\p{...}`. - Does NOT support backreferences in the pattern. - Does NOT support lookahead or lookbehind. - Flags: 'i', 'c', 'n'/'s', 'm'. ## Compatibility - Matches the common SQL REGEXP_INSTR signature (str, pattern, start, occurrence, return_opt, flags).
| Name | Type | Description |
|---|---|---|
str | Specifies the input string to search. | |
pattern | Specifies the regular expression pattern. | |
start | Specifies the 1-based character position at which to begin searching. Defaults to 1. | |
occurrence | Specifies which occurrence of the pattern to locate. Defaults to 1 (first match). Occurrences are counted starting from `start`. | |
return_opt | Specifies which position to return: 0 returns the position of the first character of the match; 1 returns the position immediately after the match. Defaults to 0. | |
flags | Specifies match flags: 'i' (case-insensitive), 'c' (case-sensitive), 'n'/'s' (dot matches newline), 'm' (multi-line). |
-- Position of the first digit
SELECT REGEXP_INSTR('abc123def', '[0-9]'); -- 4-- Position of the second digit run
SELECT REGEXP_INSTR('abc 123 def 456', '[0-9]+', 1, 2); -- 13-- End position of the match (return_opt = 1)
SELECT REGEXP_INSTR('hello world', 'world', 1, 1, 1); -- 12-- No match returns 0
SELECT REGEXP_INSTR('hello', '[0-9]'); -- 0-- Case-insensitive search
SELECT REGEXP_INSTR('Hello World', 'hello', 1, 1, 0, 'i'); -- 1-- NULL propagates
SELECT REGEXP_INSTR(CAST(NULL AS VARCHAR), 'x'); -- NULL