18 - MySQL Regular Expressions

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

Example of above patterns in a MySQL query, let’s say we have student data of a university as:

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

  1. 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

  1. 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

  1. 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

  1. 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

 

 

 

 

  1. 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

  1. 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

  1. 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}$’ 

Like us on Facebook