Returns the position of the matched substring within source, locating the nth occurrence of pattern starting from start_position.
Syntax
bigint regexp_instr(string <source>, string <pattern>[, bigint <start_position>[, bigint <occurrence>[, bigint <return_option>]]])Parameters
Required:
source: STRING. The source string to search.pattern: STRING constant or regular expression. The pattern to match. For more information, see Regular expressions. Passing an empty string raises an error.
Optional:
start_position: BIGINT constant. The character position where the search begins. Default:1.occurrence: BIGINT constant. The nth occurrence of the match to locate. Default:1.return_option: BIGINT constant. Controls whether the start or end position of the matched substring is returned. Valid values:0and1. Default:0. Passing an invalid value or a non-BIGINT value raises an error.Value Returns 0Start position of the matched substring 1End position of the matched substring
Return value
Returns a BIGINT value.
| Condition | Return value |
|---|---|
| Match found | Position of the matched substring, as specified by return_option |
| Any parameter is null | null |
pattern is an empty string | Error |
start_position or occurrence is not BIGINT, or is ≤ 0 | Error |
Usage notes
Positions are 1-based. The first character of the string is at position 1.
start_positionandoccurrencemust be positive integers. Passing0or a negative value raises an error.
Examples
All examples search the string 'i love www.taobao.com' for the pattern o[[:alpha:]]{1}.
Example 1: Return the start position of the 2nd match, starting from the 3rd character.
select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2); -- 14Example 2: Return the end position of the 2nd match, starting from the 3rd character.
select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2, 1); -- 16Example 3: A null input returns null.
select regexp_instr('i love www.taobao.com', null, 3, 2); -- nullRelated functions
REGEXP_INSTR is a string function. For related string search and conversion functions, see String functions.