MySQL provides regular expressions for pattern matching to ease the searching of data. The keyword for regular expression is REGEXP. This REGEXP operator is very similar to matching expressions via PHP or Perl codes to match the strings. It gives user very flexible way to search the data. Although it is slow searching as indexes would not work because it will not search the string from the beginning of the data except the case of one regular expression denoted as ^
This REGEXP has same standard patterns as used in all programming standards. Some of the patterns are described as:
Pattern | Matching Criteria |
^ | Denotes the beginning of string. |
$ | Denotes the ending of string |
. | Denotes any single character within string. |
[...] | Denotes any character given in the square brackets. |
[^...] | Denotes the character would not existsgiven in the square brackets |
| Matching Beginning of the words. |
| Matching Ending of the words. |
a1|a2|a3 | Denotes the matching of any of the patterns a1, a2, or a3 |
* | Denotes zero or more occurrences of preceding element |
+ | Denotes the one or more occurrences of preceding element |
{X} | Denotes the Xoccurrences of preceding element |
{X,Y} | Denotes the X through Yoccurrences of preceding element |
Admission_No | NAME | SUBJECT | YEAR |
ST_12 | Arun Paul | Chemistry | 2014 |
ST_28 | UevinLorrience | Physics | 2012 |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_91 | DeonieePesery | Home Science | 2013 |
ST_61 | Sam Piwij | Moral science | 2009 |
ST_98 | AviKheterpal | Forensic | 2010 |
- Query to find the admission number, name, subject, year having name ending with al.
SELECT Admission_No, name, subject, year from students WHERE name REGEXP ‘al$’;
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_98 | AviKheterpal | Forensic | 2010 |
- Query to find the admission number, name, subject, year having subject starts with M.
SELECT Admission_No, name, subject, year from students WHERE subject REGEXP ‘^M’;
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_61 | Sam Piwij | Moral science | 2009 |
- Query to find the admission number, name, subject, year having subject contains ‘science’.
SELECT Admission_No, name, subject, year from students WHERE subject REGEXP '[[:<:]]science[[:>:]]' ;
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_91 | DeonieePesery | Home Science | 2013 |
ST_61 | Sam Piwij | Moral science | 2009 |
- Query to find the admission number, name, subject, year having year in the range of 2008-2012.
SELECT Admission_No, name, subject, year from students WHERE Year REGEXP ‘20[08-14]’
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_28 | Kevin Lorrience | Physics | 2012 |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_61 | Sam Piwij | Moral science | 2009 |
ST_98 | AviKheterpal | Forensic | 2010 |
|
|
|
|
- Query to find the admission number, name, subject, year having name starts with any vowel.
SELECT Admission_No, name, subject, year from students WHERE name REGEXP ‘^[aeiou]’
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_12 | Arun Paul | Chemistry | 2014 |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_98 | AviKheterpal | Forensic | 2010 |
ST_28 | UevinLorrience | Physics | 2012 |
- Query to find the admission number, name, subject, year having name starts with A or T or U.
SELECT Admission_No, name, subject, year from students WHERE name REGEXP `A|T|U’
The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_12 | Arun Paul | Chemistry | 2014 |
ST_28 | UevinLorrience | Physics | 2012 |
ST_78 | Aman Aggarwal | Mathematics | 2012 |
ST_98 | AviKheterpal | Forensic | 2010 |
- Query to find the admission number, name, subject, year having number of characters in the name is exactly 8.
SELECT Admission_No, name, subject, year from students WHERE name REGEXP ‘ ^. . . . . . . .$’
Here the eight dot (.) means eight characters in total. The output of the query is:
Admission_No | NAME | SUBJECT | YEAR |
ST_12 | Arun Paul | Chemistry | 2014 |
ST_61 | Sam Piwij | Moral science | 2009 |
You can also use the below query for the specified number of characters in the search text.
SELECT Admission_No, name, subject, year from students WHERE name REGEXP ‘ ^.{12}$’