search
Search
Login
Unlock 100+ guides
menu
menu
web
search toc
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
What does this mean?
Why is this true?
Give me some examples!
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to

MySQL | REGEXP_INSTR method

schedule Aug 12, 2023
Last updated
local_offer
MySQL
Tags
mode_heat
Master the mathematics behind data science with 100+ top-tier guides
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

c

Case sensitive matching.

i

Case-insensitive matching.

m

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.

n

The . character matches line terminators. Default is for . matching to stop at the end of a line.

u

Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

Return value

The return value depends on the follow cases:

Case

Return value

No match found

0

return_option=0

Index position of the first character of the matched substring.

return_option=1

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.

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!