MySQL REGEXP_INSTR() Function
REGEXP_INSTR() function in MySQL is used for pattern matching. It is a function that returns the index value of the substring by matching the given string. This function returns 0 if no match is found. Else, it returns 1. If either expression or pattern is NULL, it returns NULL. Here the indexing of characters starts at 1.
Syntax
The following is a basic syntax to use this function in MySQL:
Parameter Explanation
The explanation of the REGEXP_INSTR() function parameters are:
expression: It is a string on which we will perform searching through regular expressions parameters and functions.
patterns: It represents the regular expression character to be used for matching.
The REGEXP_INSTR() function uses various optional parameters that are given below:
pos: It is used to specify the position in expression at which the search will starts. If we will not specify this parameter, by default, it 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: It represents a case-sensitive matching.
- i: It represents a case-insensitive matching.
- m: It represents a multiple-line mode that allows line terminators within the string. By default, this function matches line terminators at the start and end of the string.
- n: It is used to modify the . (dot) character to match line terminators.
- u: It represents Unix-only line endings.
Let us understand it with various examples.
Example
This statement returns the starting index of substring ‘BCA‘ within the expression. If we execute the statement, it will return 1, which is the given string’s first index. It is because here, we have not specified any optional parameters.
See the below output:
In this statement, we locate the appearance of the pattern string ‘BCA’ to the second time by modifying the query with optional parameters position and occurrence.
See the below output:
In this statement, we have uses the return_option parameter. This parameter is useful when we want to get information that is preceded by some kind of identifier.
See the below output:
