MySQL Regular Expressions (Regexp)

Previous Page
Next Page

Regular expressions or regexes are strings that define a pattern. We can use regexes to check if the data contains a string of digits or strings of alphabets or some pattern.

The main advantage of using regexes is that we can search Strings using more than just Like and % operators.

My SQL supports the regexp, rlike, not regexp operator for supporting regex patterns. The Basic syntax of using a regexp operator is

SELECT <column_list>
FROM <table-names>
WHERE <column of String datatype> REGEXP pattern;

SELECT <column_list>
FROM <table-names>
WHERE <column of String datatype> RLIKE pattern;

SELECT <column_list>
FROM <table-names>
WHERE <column of String datatype> NOT REGEXP pattern;

The regexp needs to be with a column type of String. If the column value matches the pattern, a resultSet is returned. If no matches found then NULL is returned.

To construct regex patterns, we use metacharacters. The metacharacters that can be used are as follows:

Metacharacter Behavior
^ matches the position at the beginning of the searched string
$ matches the position at the end of the searched string
. matches any single character
[…] matches any character specified inside the square brackets
[^…] matches any character not specified inside the square brackets
p1|p2 matches any of the patterns p1 or p2
* matches the preceding character zero or more times
+ matches preceding character one or more times
{n} matches n number of instances of the preceding character
{m,n} matches from m to n number of instances of the preceding character

The Advantage of Regex patterns is that we can expand the searching of various patterns. The disadvantage of the regex is that they are difficult to maintain.  

Some Examples:

Let us look at a few examples of Regex strings we would use in MySQL.

1. If we want to check for all strings that start with the letter 'A', the regex is 

SELECT * FROM FILM WHERE TITLE REGEXP '^A';

2. To check all the strings that do not start with A

SELECT * FROM FILM WHERE TITLE REGEXP '^[^A]';

 

3. To get all such records that have the last updated date other than the year 2006.

SELECT title,last_update FROM FILM WHERE LAST_UPDATE  NOT REGEXP '^2006+';

4. To get all such names which have the pattern <some character between H and M> + one character+ T

For example The name: Matthew  M (one character between H & M) + A (one single character) + T 

SELECT FIRST_NAME FROM ACTOR WHERE FIRST_NAME REGEXP '[H-M].[T]' ;

Previous Page
Next Page