The REGEXP_INSTR function searches a string for a POSIX-style regular expression. This function returns the position within the string where the match is located.
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
Parameter | Description |
---|---|
srcstr | The string to search. |
pattern | The regular expression for which REGEXP_INSTR will search. |
position | An integer value that indicates the start position in a source string. The default value is 1. |
occurrence | Specifies which match is returned if more than one occurrence of the pattern occurs in the string that is searched. The default value is 1. |
returnparam | An integer value that specifies the location within the string that REGEXP_INSTR returns
as expected. The default value is 0. Specify:
|
modifier | The values that control the pattern matching behavior. The default value is NULL. For a complete list of the modifiers supported by PolarDB, see the PostgreSQL core documentation. |
subexpression | An integer value that identifies the portion of the pattern that will be returned
by REGEXP_INSTR. The default value of subexpression is 0.
If you specify a value for subexpression, you must include one (or multiple) set of parentheses in the pattern to isolate a portion of the value being searched. The value specified by subexpression indicates which set of parentheses will be returned. For example, if the value of subexpression is 2, REGEXP_INSTR returns the value contained within the second set of parentheses. |
Examples
In the following example, REGEXP_INSTR searches a string that contains a phone number for the first occurrence of a pattern that contains 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 command instructs REGEXP_INSTR to return the position of the first occurrence. If you want to return the start of the second occurrence of three consecutive digits, use the following command:
polardb=# SELECT REGEXP_INSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 2) FROM DUAL;
regexp_instr
--------------
5
(1 row)