Regular expressions, used in most of other programming languages, are also well established in MySQL where they may be used to specify patterns for search operation in a database. The REGEXP keyword along with wildcards give powerful tool for search throughout the recordset. They are normally not case-sensitive except when used with binary strings. The table below shows the wildcard characters that can be used in search patterns:

Wildcards in MySQL
Wildcards Example Description
^ SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP '^sh'; "^" matches the beginning of string. The example searches and display data of NameOfStudents beginning from 'sh'
$ SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP 'sh$'; "$" matches the end of string. The example searches and display data of NameOfStudents ending with 'sh'
. SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP 'sh.'; "." matches any character of string. The example searches and display data of NameOfStudents with 'sh' in the string
[...] SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP '[a-d]'; '[]' matches any character listed between the square brackets. The example searches and display data of NameOfStudents with 'a','b','c','d' in the string
[^...] SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP '^[aeio]'; '^[]' matches any string which does not contain character listed between the square brackets. The example searches and display data of NameOfStudents without having 'a','e','i','o' in the string
str1| str2| str3 SELECT * FROM `tblstudent` WHERE `NameOfStudent` REGEXP 'tony|james|gene'; "|" matches any of the patterns in str1, str2, or str3. The example searches and display data of NameOfStudents with 'tony', 'james' or 'gene'
* SELECT * FROM ` tblstudent ` WHERE ` NameOfStudent ` REGEXP '^[s].*t$'; "*" matches zero or more instances of preceding element. This example displays data beginning with 's' and ends with 't' with zero or more characters in-between
+ SELECT * FROM ` tblstudent ` WHERE ` NameOfStudent ` REGEXP ' ^[Sh]a+'; '+' displays one or more instances of preceding element. This example displays data beginning with 's' or 'h' and has one or more 'a' character
{n} SELECT * FROM ` tblstudent ` WHERE ` NameOfStudent ` REGEXP ' ^[a-f]{7}'; {n} matches 'n' instances of preceding element. The example displays names of students starting from 'a','b' ... 'f' and is exactly 7 characters long. Ex. "Collins". Names like "Fisher" is not displayed
{m,n} SELECT * FROM ` tblstudent ` WHERE ` NameOfStudent ` REGEXP ' ^[a-f]{5,7}'; {m,n} displays m through n instances of preceding element. The example displays names of students starting from 'a','b' ... 'f' and length of the name is from 5 to 7 characters long.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTE: to get more complex search patterns specific to one's needs, one or more wild cards given above can be combined together.