A regular expression is a character sequence that is used to search for and locate specific character sequences that match a pattern. You can, therefore, use it for searching text or validating input. Apart from the use of wildcards, SQL supports pattern matching operations based on the regular expressions and the REGEXP operator.

At times, you may find that wildcards do not provide enough flexibility and must instead rely on regular expressions to construct search expressions. Alluding to a popular joke, a regular expression is essentially a search expression on steroids. It's far too broad a subject to cover in this article, so I'll just introduce you to it.

The basic syntax for a regular expression is as follows:

SELECT statements... WHERE fieldname REGEXP 'pattern';

Let's now look at a practical example:

SELECT first_name, last_name FROM customer WHERE last_name REGEXP 'ITH';

The above query searches for all the customers whose last names have the word "ITH" in them. It does not matter whether the “ITH” is at the beginning, middle, or end of the last name. As long as it is contained in the last name then it will be considered.

Assume we want to find all customers whose last name starts with Q or Y followed by any number of other characters; how would we go about doing so? To achieve the desired results, we can use a regular expression in conjunction with the metacharacters.

SELECT first_name, last_name FROM customer WHERE last_name REGEXP '^[QY]';

The regexp operator applies a regular expression ('[QY]' in this example) to the expression on the left side of the condition (the column last name). Instead of two conditions, as would have been the case using wildcard characters, the query now has a single condition using a regular expression. The caret (^), which is a metacharacter, means that the pattern match should be applied at the beginning. A metacharacter is a character with a unique meaning during pattern processing. They allow us to fine-tune our pattern search results using regular expressions.

There are other metacharacters used for advanced regular expression pattern matches. Try researching on them and see how they can be used.

Summary

• Regular expressions offer a powerful and flexible pattern match that can aid in the implementation of powerful search utilities for our database systems.
• REGEXP is the operator used to match regular expression patterns.
• Regular expressions support a number of metacharacters that provide greater flexibility and control when performing pattern matching.
• Regular expressions are not case sensitive.