MySQL | Regular Expressions (Regexp) (original) (raw)

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