MySQL Regular Expressions

In the previous chapters, we have learned that MySQL can useLIKE ...%for fuzzy matching.

MySQL also supports matching other regular expressions. In MySQL, theREGEXPandRLIKEoperator is used for regular expression matching.

If you are familiar with PHP or Perl, it is very simple to operate, because MySQL's regular expression matching is similar to these scripts.

The regular expression patterns in the following table can be applied to the REGEXP operator.

PatternDescription
^Matches the beginning position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after '\n' or '\r'.
$Matches the end position of the input string. If the Multiline property of the RegExp object is set, $ also matches the position before '\n' or '\r'.
.Matches any single character except "\n". To match any character including '\n', use a pattern like '[.\n]'.
[...]Character set. Matches any one of the contained characters. For example, '[abc]' can match 'a' in "plain".
[^...]Negated character set. Matches any character not contained. For example, '[^abc]' can match 'p' in "plain".
p1|p2|p3Matches p1 or p2 or p3. For example, 'z|food' can match "z" or "food". '(z|f)ood' matches "zood" or "food".
*Matches the preceding subexpression zero or more times. For example, zo* can match "z" and "zoo". * is equivalent to {0,}.
+Matches the preceding subexpression one or more times. For example, 'zo+' can match "zo" and "zoo", but cannot match "z". + is equivalent to {1,}.
{n}n is a non-negative integer. Matches exactly n times. For example, 'o{2}' cannot match 'o' in "Bob", but can match the two o's in "food".
{n,m}m and n are both non-negative integers, where n <= m. Matches at least n times and at most m times.

Character Classes for Regular Expression Matching

  • .: matches any single character.
  • ^: matches the beginning of a string.
  • $: matches the end of a string.
  • *: matches zero or more of the preceding elements.
  • +: matches one or more of the preceding elements.
  • ?: matches zero or one of the preceding elements.
  • [abc]: matches any one character in the character set.
  • [^abc]: matches any character except those in the character set.
  • [a-z]: matches any lowercase letter in the range.
  • [0-9]: matches a digit character.
  • \w: matches an alphanumeric character (including underscore).
  • \s: matches a whitespace character.

Pattern Matching Using REGEXP

REGEXP is an operator used for regular expression matching.

REGEXP is used to check whether a string matches a specified regular expression pattern. The following is the basic syntax of the REGEXP operator:

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';

Parameter description:

  • column1, column2, ... are the names of the columns you want to select. If you use*it means selecting all columns.
  • table_nameis the name of the table from which you want to query data.
  • column_nameis the name of the column on which you want to perform regular expression matching.
  • 'pattern'is a regular expression pattern.

Find all data in the name field that starts with'st'as the beginning:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Find all data in the name field that ends with'ok'as the ending:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Find all data in the name field that contains'mar'string:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Find all data in the name field that starts with a vowel character or ends with'ok'string:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

Select records from the orders table where the description contains "item" followed by one or more digits.

SELECT * FROM orders WHERE order_description REGEXP 'item[0-9]+';

Use theBINARYkeyword to make the matching case-sensitive:

SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';

Use OR for multiple matching conditions. The following will select employee records whose last name is "Smith" or "Johnson":

SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';

Pattern Matching Using RLIKE

RLIKE is an operator in MySQL used for regular expression matching. It is the same as REGEXP. RLIKE and REGEXP can be used interchangeably with no difference.

The following is the basic syntax for regular expression matching using RLIKE:

SELECT column1, column2, ...
FROM table_name
WHERE column_name RLIKE 'pattern';

Parameter description:

  • column1, column2, ... are the names of the columns you want to select. If you use*it means selecting all columns.
  • table_nameis the name of the table from which you want to query data.
  • column_nameis the name of the column on which you want to perform regular expression matching.
  • 'pattern'is a regular expression pattern.
SELECT * FROM products WHERE product_name RLIKE '^[0-9]';

The above SQL statement selects all products whose product names start with a digit.

Other Extensions