REGEXP_INSTR searches a string for a POSIX regular expression pattern and returns the position of the match.
Syntax
INTEGER REGEXP_INSTR
(
srcstr TEXT,
pattern TEXT,
position INT DEFAULT 1,
occurrence INT DEFAULT 1,
returnparam INT DEFAULT 0,
modifier TEXT DEFAULT NULL,
subexpression INT DEFAULT 0,
)Parameters
Required
| Parameter | Description |
|---|---|
srcstr | The string to search. |
pattern | The regular expression pattern to match. |
Optional
| Parameter | Default | Description |
|---|---|---|
position | 1 | The character position in srcstr where the search starts. |
occurrence | 1 | Which match to return when the pattern appears more than once. Skips the first occurrence - 1 matches. For example, occurrence => 2 skips the first match and returns the position of the second. |
returnparam | 0 | Controls what position is returned: 0 returns the position of the first character of the match; any value greater than 0 returns the position of the first character after the end of the match. |
modifier | NULL | Flags that control pattern matching behavior. For the full list, see the PostgreSQL documentation. |
subexpression | 0 | Identifies which captured group to locate. When set to a non-zero value, include one or more sets of parentheses in pattern to isolate a portion of the value being searched. For example, subexpression => 2 returns the position of the second captured group. |
Examples
Find the first occurrence of a pattern
Search '800-555-1212' for the first group of three consecutive digits:
polardb=# SELECT REGEXP_INSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 1) FROM DUAL;
regexp_instr
--------------
1
(1 row)The match starts at position 1 (800).
Find the second occurrence of a pattern
Pass 2 for occurrence to skip the first match and locate the second:
polardb=# SELECT REGEXP_INSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 2) FROM DUAL;
regexp_instr
--------------
5
(1 row)The second match (555) starts at position 5.