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.


  srcstr        TEXT, 
  pattern       TEXT, 
  position      INT  DEFAULT 1,
  occurrence    INT  DEFAULT 1,
  returnparam   INT  DEFAULT 0,
  modifier      TEXT DEFAULT NULL,
  subexpression INT  DEFAULT 0,


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:
  • 0 to return the location within the string of the first character that matches the pattern.
  • A value greater than 0 to return the location of the first character following the end of the pattern.
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.


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;
(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;
(1 row)