MySQL | Regular Expressions (Regexp)

In MySQL, regular expressions (REGEX) offer powerful functionality for flexible pattern matching within string data. By using the REGEXP and RLIKE operators, developers can efficiently search, validate, and manipulate string data in more dynamic ways than simple LIKE queries.

In this article, we will explain how to use SQL regular expressions, cover metacharacters, provide real-world examples, and discuss the use cases of REGEX in SQL for better query optimization and performance.

What is SQL REGEX in MySQL?

SQL REGEX refers to a powerful string-matching mechanism supported by MySQL for complex search operations. It allows developers to define custom search patterns to match strings, making it an essential tool for querying large datasets, filtering information, or validating data in database columns.

In MySQL, REGEXP and RLIKE are synonymous operators used for performing regular expression matching. The primary difference between REGEXP and RLIKE is that REGEXP is the standard operator, while RLIKE is just an alias for it.

How SQL REGEX Works in MySQL

The REGEXP operator allows advanced pattern matching capabilities, which are especially useful for performing complex searches or validations. It differs from the LIKE operator because it supports a wider variety of matching patterns and metacharacters.

Key Features:

• Advanced Pattern Matching: REGEXP enables more complex searches, such as matching specific character classes, ranges, and repetitions.
• Escape Sequences: The backslash (\) is used as an escape character in regular expressions. To use a literal backslash, you must escape it with another backslash (i.e., \\).
• Case Insensitivity: By default, MySQL regular expressions are case-insensitive. To make them case-sensitive, you can use the BINARY keyword.

Commonly Used Metacharacters in SQL REGEX

MySQL’s regular expressions support a variety of metacharacters that allow complex pattern creation. Below is a list of commonly used metacharacters in regex in SQL and their functions:

Examples of SQL REGEX in MySQL

Here are several practical examples demonstrating how to use SQL regular expressions in MySQL queries to achieve dynamic pattern matching:

1. Match beginning of string(^)

To match all names starting with “sa“, we use the caret (^) symbol to specify the beginning of the string.

Example:

SELECT name FROM student_tbl WHERE name REGEXP '^sa';

This will match names like sam and samarth.

2. Match the end of a string($)

To match all names ending with “on”, we use the dollar sign ($) to signify the end of the string.

Example:

SELECT name FROM student_tbl WHERE name REGEXP 'on$';

This will match names like norton and merton.

3. Match zero or one instance of the strings preceding it(?)

To find all movie titles containing “com” with optional spaces following it (e.g., “com” or “com “), you can use the question mark (?)

Example:

SELECT title FROM movies_tbl WHERE title REGEXP 'com ?'; 

This will match titles like comedy and romantic comedy

4. Matches any of the patterns p1, p2, or p3(p1|p2|p3)

The pipe ( | ) operator allows you to match any of the specified patterns. In this case, it matches names that contain either “be” or “ae”.

Example:

SELECT name FROM student_tbl WHERE name REGEXP 'be|ae' ;

This will match names like Abel and Baer.

5. Matches any character listed between the square brackets([abc])

The square brackets [] in SQL regular expressions allow us to match any single character listed inside them. Gives all the names containing ‘j’ or ‘z’ anywhere in the string.

Example:

SELECT name FROM student_tbl WHERE name REGEXP '[jz]' ;

This will match names like Lorentz and Rajs

6. Matches any lower case letter between ‘a’ to ‘z’- ([a-z]) ([a-z] and (.))

The pattern ([a-z] and .) in SQL regex is used to match a lowercase letter within a specified range, followed by any single character, and then another specified character. This query will retrieve names containing a letter between ‘b’ and ‘g’, followed by any character, and ending with the letter ‘a’.

Example:

SELECT name FROM student_tbl WHERE name REGEXP '[b-g].[a]' ;

This will match names like Tobias and sewall

7. Matches any character not listed between the square brackets.([^abc])

The pattern [^abc] in SQL regex is used to match any character that is not listed between the square brackets. This query retrieves all names that do not contain the characters ‘j’ or ‘z’.

Example:

SELECT name FROM student_tbl WHERE name REGEXP '^[^jz]*$';

This will exclude names containing ‘j’ or ‘z’, such as nerton and sewall

8. Matches the end of words[[:>:]]

The pattern [[:>:]] in SQL regex is a word boundary metacharacter that matches the end of a word. This query retrieves all titles where a word ends with “ack”.

Example:

SELECT title FROM movies_tbl WHERE REGEXP 'ack[[:>:]]'; 

This will match titles like Black.

9. Matches the beginning of words[[:<:]]

The pattern [[:<:]] in SQL regex is a word boundary metacharacter that matches the beginning of a word. Gives all the titles starting with character “for“.

Example:

SELECT title FROM movies_tbl WHERE title REGEXP '[[:<:]]for'; 

This will match titles like Forgetting Sarah Marshall

10. Matches a character class[:class:]

The [:class:] pattern in SQL regular expressions matches a specific character class, providing flexibility in filtering data based on character types. Gives all the titles containing alphabetic character only.

Example:

SELECT title FROM movies_tbl WHERE REGEXP '[:alpha:]' ;

This will match titles like Stranger Things and Avengers

11. Matches the beginning of all words by any character listed between the square brackets.(^[abc])

The pattern ^[abc] matches strings where the first character of the word belongs to the specified set of characters (in this case, a, b, or c). Gives all the names starting with ‘n‘ or ‘s‘.

Example:

SELECT name FROM student_tbl WHERE name REGEXP '^[ns]' ;

This will match names like nerton and sewall

Conclusion

SQL REGEX in MySQL offers advanced pattern matching capabilities that allow us to perform complex queries and improve data manipulation in our databases. By using REGEXP and RLIKE operators, we can create dynamic search conditions that are highly useful for validating, filtering, and retrieving data. Understanding regular expressions will greatly enhance our SQL querying skills and improve database performance, especially for complex datasets.