MySQL regexp_instr() Function
The Regexp_instr is a new function introduced in MySQL 8.0. This function returns the starting index of the substring that matches the regex pattern.
The Basic syntax of the regexp_instr function is
REGEXP_INSTR(expression, patterns, optional parameters);
Where expression = the string to be searched
Patterns = the regex pattern
There are 4 optional parameters that we can use :
pos: this is the offset i.e. the starting index from which starting should start. By default, pos is 1.
occurrence: It is used to specify for which occurrence of a match we are going to search. If we will not specify this parameter, by default, it is 1.
return_option: It is used to specify which type of position of the matched substring will return. If its value is 0, it means the function returns the position of the matched substring's first character. If its value is 1, it will return the position following the matched substring. If we will not specify this parameter, by default, it is 1.
match_type: It is a string that uses the following possible characters to perform matching.
- c: case-sensitive matching.
- i: case-insensitive matching.
- m: a multiple-line mode that allows line terminators within the strings. By default, this function matches line terminators at the start and end of the string.
- n: modify the
.(dot) character to match line terminators. - u: Unix-only line endings.
Examples:
-
Basic usage for regexp_instr() function
select regexp_instr('to be, or not to be, that is the question.','be');
2. Basic usage for regexp_instr() function with a regex
select regexp_instr('to be, or not to be, that is the question.','n.t');
3. Basic usage regexp_instr() function with regex and an offset
We are searching for both patterns, one with an offset and one without
select regexp_instr('to be, or not to be, that is the question.','[n,t,q]'),
regexp_instr('to be, or not to be, that is the question.','[n,t,q]',3);
4. To find the second occurrence of the regex in a string
In this example, we are checking for a string in a bigger string and getting the second occurrence of the string after searching from an offset. We also see the output of the same without considering an offset.
select regexp_instr('good night, good night! parting is such sweet sorrow, That I shall say good night till it be morrow.','good',2),
regexp_instr('good night, good night! parting is such sweet sorrow, That I shall say good night till it be morrow.','good',5,2);
