How to use the regexp_instr function in mysql

1. The function returns the substring position matching the pattern pat in the string expr. If no matching substring is found, 0 is returned.

2. If expr or pat is NULL, the function returns NULL. Character positions start from 1.

Optional parameters:

pos means starting to search from the specified position of the string expr. By default, matching starts from the 1st character.

occurrence means how many matching results are returned. By default, the position of the first matched substring is returned.

return_option indicates the type of return location. If this parameter is 0 (the default value), the position of the first character of the substring is returned; if this parameter is 1, the position of the first character after the substring is returned.

match_type indicates the matching method, and the parameters are consistent with the REGEXP_LIKE() function above.

Example

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+

The above is the detailed content of How to use the regexp_instr function in mysql. For more information, please follow other related articles on the PHP Chinese website!