MySQL | REGEXP_INSTR method
Start your free 7-days trial now!
MySQL's REGEXP_INSTR(~) method returns the starting index of the substring matching the regular expression pat.
Parameters
1. expr | string
The string to check for matches with regular expression pattern.
2. pat | regular expression
The regular expression pattern for matching.
3. pos | integer | optional
The position in expr at which to start the search. Defaults to 1.
4. occurrence | number | optional
Which occurrence of a match to return the index for. Defaults to 1.
5. return_option | 0 or 1 | optional
0: returns the position of first character of matched substring. Default behavior.
1: returns the position of the character following the matched substring.
6. match_type | string | optional
Specifies how to perform matching. Multiple characters may be specified, however, if there is a contradiction between the provided match_types the match_type on the right take precedence.
match_type | Meaning |
|---|---|
| Case sensitive matching. |
| Case-insensitive matching. |
| Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression. |
| The |
| Unix-only line endings. Only the newline character is recognized as a line ending by the |
Return value
The return value depends on the follow cases:
Case | Return value |
|---|---|
No match found | 0 |
| Index position of the first character of the matched substring. |
| Index position of character following the matched substring. |
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To return index position of first occurrence of i or e in student first names:
SELECT fname, REGEXP_INSTR(fname,'[ie]')FROM students;
+----------+----------------------------+| fname | REGEXP_INSTR(fname,'[ie]') |+----------+----------------------------+| Sky | 0 || Ben | 2 || Travis | 5 || Arthur | 0 || Benjamin | 2 |+----------+----------------------------+
Position
To start regex matching from position 3:
SELECT fname, REGEXP_INSTR(fname,'[ie]', 3, 1, 0, 'c')FROM students;
+----------+------------------------------------------+| fname | REGEXP_INSTR(fname,'[ie]', 3, 1, 0, 'c') |+----------+------------------------------------------+| Sky | 0 || Ben | 0 || Travis | 5 || Arthur | 0 || Benjamin | 7 |+----------+------------------------------------------+
Note that any occurrences of i or e before position 3 are ignored.
Occurence
To return the second occurrence of i or e in student first names:
SELECT fname, REGEXP_INSTR(fname, '[ie]', 1, 2, 0, 'c')FROM students;
+----------+-------------------------------------------+| fname | REGEXP_INSTR(fname, '[ie]', 1, 2, 0, 'c') |+----------+-------------------------------------------+| Sky | 0 || Ben | 0 || Travis | 0 || Arthur | 0 || Benjamin | 7 |+----------+-------------------------------------------+
Note that only Benjamin has two occurrences of i or e in fname.
Return option
Reminder of the students table we are working with:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
To set return option=1 to return the position of the character after the match:
SELECT fname, REGEXP_INSTR(fname,'[ie]', 1, 1, 1, 'c')FROM students;
+----------+------------------------------------------+| fname | REGEXP_INSTR(fname,'[ie]', 1, 1, 1, 'c') |+----------+------------------------------------------+| Sky | 0 || Ben | 3 || Travis | 6 || Arthur | 0 || Benjamin | 3 |+----------+------------------------------------------+
Note that we return the index position of the character after the first occurrence of i or e in first names.
Match type
To perform a case sensitive match by specifying match_type='c':
SELECT fname, REGEXP_INSTR(fname,'[a]', 1, 1, 0, 'c')FROM students;
+----------+-----------------------------------------+| fname | REGEXP_INSTR(fname,'[a]', 1, 1, 0, 'c') |+----------+-----------------------------------------+| Sky | 0 || Ben | 0 || Travis | 3 || Arthur | 0 || Benjamin | 5 |+----------+-----------------------------------------+
Note that we return 0 for Arthur as we only look for matches with lowercase a.
