SQL Tutorial SQL Advanced SQL Database SQL References

MySQL REGEXP_INSTR() Function



The MySQL REGEXP_INSTR() function returns the starting index of a regular expression pattern in the given string. It returns 0 if there is no match. If the string or pattern is NULL, the return value will be NULL. Character indexes begin at 1.

By default, REGEXP_INSTR() function performs a case-insensitive match, except when used with binary strings.

Syntax

REGEXP_INSTR(string, pattern, position, occurrence, return_option, match_type)

Parameters

string Required. Specify the string to search.
pattern Required. Specify the regular expression matching information. See the table below for pattern syntax.
position Optional. Specify the position in the string at which to start the search. Default is 1.
occurrence Optional. Specify which occurrence of a match to search for. Default is 1.
return_option Optional. Specify which type of position to return. If this value is 0, the function returns the position of the matched substring's first character. If this value is 1, the function returns the position following the matched substring. Default is 0.
match_type Optional. Specify how to perform matching. It can take following values:
  • 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. The 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

For additional information about this parameter, see the examples of REGEXP_LIKE() function.

Regular Expression Patterns

SymbolDescription
^Matches the beginning of a string.
$Matches the end of a string.
*Matches zero or more occurrences.
+Matches one or more occurrences.
?Matches zero or one occurrence.
.Matches any character except NULL.
|Used like an "OR" to specify more than one alternative.
[ ]Matches any single character specified within [].
[^ ]Matches any single character that is not specified within [].
-Represents a range of characters.
( )Used to group expressions as a subexpression.
{m}Matches m times.
{m,}Matches at least m times.
{m,n}Matches at least m times, but no more than n times.
\nn is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
\dMatches a digit character.
\DMatches a nondigit character.
\wMatches a word character.
\WMatches a nonword character.
\sMatches a whitespace character.
\Smatches a non-whitespace character.
*?Matches the preceding pattern zero or more occurrences.
+?Matches the preceding pattern one or more occurrences.
??Matches the preceding pattern zero or one occurrence.
{n}?Matches the preceding pattern n times.
{n,}?Matches the preceding pattern at least n times.
{n,m}?Matches the preceding pattern at least n times, but not more than m times.
[..]Matches one collation element that can be more than one character.
[=character_class=]Represents an equivalence class. It matches all characters with the same collation value, including itself.
[:character_class:]Represents a character class that matches all characters belonging to that class.

List of standard class name

Character Class NameMeaning
alnumAlphanumeric characters
alphaAlphabetic characters
blankWhitespace characters
cntrlControl characters
digitDigit characters
graphGraphic characters
lowerLowercase alphabetic characters
printGraphic or space characters
punctPunctuation characters
spaceSpace, tab, newline, and carriage return
upperUppercase alphabetic characters
xdigitHexadecimal digit characters

Example: REGEXP_INSTR() examples

The example below shows the usage of REGEXP_INSTR() function.

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
Result: 1

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
Result: 9

mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
Result: 1

mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
Result: 8

Example: Match on more than one alternative

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4SmithAmsterdam303100
5AmanNew Delhi283000

The below query is used to get the location of first occurrence of vowel in the records of Name column. To specify more than one alternative in the pattern, | is used.

SELECT *, 
REGEXP_INSTR(Name, 'a|e|i|o|u') AS first_occurrence
FROM Employee

This will produce a result similar to:

EmpIDNameCityAgeSalaryfirst_occurrence
1JohnLondon2530002
2MarryNew York2427502
3JoParis2728002
4SmithAmsterdam3031003
5AmanNew Delhi2830001

Example: Matching on multiple characters

Consider the above discussed table. The below query is used to get the location of first occurrence of "ar" or "er" (whichever comes first) in the records of City column. To specify more than one alternative in the pattern, [  ] is used

SELECT *, 
REGEXP_INSTR(City, '[ae]r') AS first_occurrence
FROM Employee

This will produce a result similar to:

EmpIDNameCityAgeSalaryfirst_occurrence
1JohnLondon2530000
2MarryNew York2427500
3JoParis2728002
4SmithAmsterdam3031005
5AmanNew Delhi2830000

❮ MySQL Functions