REGEXP_SUBSTR searches a string for a pattern specified by a POSIX regular expression and returns the matched substring.
Syntax
TEXT REGEXP_SUBSTR
(
srcstr TEXT,
pattern TEXT,
position INT DEFAULT 1,
occurrence INT DEFAULT 1,
modifier TEXT DEFAULT NULL,
subexpression INT DEFAULT 0
)Parameters
| Parameter | Description |
|---|---|
srcstr | The string to search. |
pattern | The POSIX regular expression to match against. |
position | The character position in srcstr where the search starts. Default: 1 (the beginning of the string). |
occurrence | Which match to return when the pattern appears more than once. Default: 1 (the first match). |
modifier | Flags that control pattern matching behavior. Default: NULL. For supported values, see the PostgreSQL core documentation. |
subexpression | Identifies which parenthesized group in the pattern to return. Default: 0 (returns the full match). Subexpressions are numbered in the order of their opening parentheses. For example, subexpression => 2 returns the text captured by the second (...) group in the pattern. If you specify a value for subexpression, you must include one or more sets of parentheses in the pattern to isolate the portion of the value being searched. |
Examples
Extract a substring by occurrence
The following examples search a phone number string for groups of three consecutive digits.
Return the first match (occurrence => 1):
polardb=# SELECT REGEXP_SUBSTR('800-555-****', '[0-9][0-9][0-9]', 1, 1) FROM DUAL;
regexp_substr
---------------
800
(1 row)Return the second match (occurrence => 2):
polardb=# SELECT REGEXP_SUBSTR('800-555-****', '[0-9][0-9][0-9]', 1, 2) FROM DUAL;
regexp_substr
---------------
555
(1 row)