How REGEXP works in SQL? Best REGEXP examples

Regular expressions (REGEXP) in SQL are powerful tools that allow you to search, match, and manipulate strings with incredible flexibility. If you’ve ever needed to perform complex text searches within a database, REGEXP is something you should absolutely master. In this article, I’ll walk you through how REGEXP works in SQL, showcase its practical applications, and provide some of the best REGEXP examples in action.

What is REGEXP in SQL?

REGEXP (short for Regular Expression) is a pattern-matching functionality used in SQL databases to search for strings that match certain rules. Unlike simple LIKE statements, which are limited to basic wildcards, REGEXP allows for complex search patterns using metacharacters and character classes.

Different SQL implementations offer REGEXP support, including MySQL, MariaDB, PostgreSQL, and even some versions of Oracle and SQL Server. However, syntax and support may slightly vary depending on the database engine you’re using.

Basic REGEXP Syntax

To use REGEXP in SQL queries, you typically apply it with the REGEXP keyword in a WHERE clause like this:

SELECT * FROM users WHERE name REGEXP '^[A-Z]';

This query retrieves all users whose names start with an uppercase letter.

Common REGEXP Operators

SQL REGEXP uses different metacharacters to define patterns. Below is a quick reference guide:

Operator	Meaning	Example
`.`	Matches any character	`'b.d'` matches “bad”, “bud”, “bed” etc.
`^`	Matches the beginning of a string	`'^hello'` matches “hello world” but not “world hello”
`$`	Matches the end of a string	`'world$'` matches “Hello world” but not “world Hello”
`[abc]`	Matches any character listed	`'[aeiou]'` matches any vowel
`[a-z]`	Matches any character in a range	`'[0-9]'` matches any digit
`*`	Matches zero or more of the preceding character	`'go*d'` matches “gd”, “god”, “good”, “goood”, etc.
`+`	Matches one or more of the preceding character	`'go+d'` matches “god”, “good”, “goood”, etc. but not “gd”
`?`	Matches zero or one of the preceding character	`'go?d'` matches “gd” or “god” but not “good”
`\d`	Matches any digit (0-9)	`'\d{3}'` matches any three-digit sequence

Best REGEXP Examples in SQL

1. Finding Email Addresses

Want to find all users with a Gmail address? This query helps:

SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\\.com

2. Extracting Phone Numbers

Need to filter records that contain a US-style phone number?

SELECT * FROM contacts WHERE phone REGEXP '^\\(\\d{3}\\) \\d{3}-\\d{4}

3. Finding Strings That Do Not Contain Numbers

If you need to find all names without numbers, try this:

SELECT * FROM users WHERE name NOT REGEXP '[0-9]';

4. Searching for Specific Words

If you want to search for records containing the word “SQL” (case insensitive), use:

SELECT * FROM articles WHERE content REGEXP 'SQL';

Performance Considerations When Using REGEXP

While REGEXP is powerful, using it carelessly may impact database performance. Here are some best practices to keep queries efficient:

Use indexed columns whenever possible.
Avoid performing REGEXP operations on large datasets unless absolutely necessary.
Optimize queries by limiting the number of records to be checked.
If searching for specific words, consider full-text indexing as an alternative.

Conclusion

SQL REGEXP is a fantastic tool for working with string patterns efficiently. Whether you’re filtering email addresses, formatting phone numbers, or validating inputs, mastering REGEXP will significantly enhance your SQL skills. While it does have a learning curve, once grasped, it can replace multiple complex OR conditions and LIKE statements. Use it wisely, optimize your queries, and enjoy the power of regular expressions in SQL.

“`

2. Extracting Phone Numbers

Need to filter records that contain a US-style phone number?

3. Finding Strings That Do Not Contain Numbers

If you need to find all names without numbers, try this:

4. Searching for Specific Words

If you want to search for records containing the word “SQL” (case insensitive), use:

Performance Considerations When Using REGEXP

While REGEXP is powerful, using it carelessly may impact database performance. Here are some best practices to keep queries efficient:

Use indexed columns whenever possible.
Avoid performing REGEXP operations on large datasets unless absolutely necessary.
Optimize queries by limiting the number of records to be checked.
If searching for specific words, consider full-text indexing as an alternative.

Conclusion

“`

3. Finding Strings That Do Not Contain Numbers

If you need to find all names without numbers, try this:

4. Searching for Specific Words

If you want to search for records containing the word “SQL” (case insensitive), use:

Performance Considerations When Using REGEXP

While REGEXP is powerful, using it carelessly may impact database performance. Here are some best practices to keep queries efficient:

Use indexed columns whenever possible.
Avoid performing REGEXP operations on large datasets unless absolutely necessary.
Optimize queries by limiting the number of records to be checked.
If searching for specific words, consider full-text indexing as an alternative.

Conclusion

“`

;

2. Extracting Phone Numbers

Need to filter records that contain a US-style phone number?

3. Finding Strings That Do Not Contain Numbers

If you need to find all names without numbers, try this:

4. Searching for Specific Words

If you want to search for records containing the word “SQL” (case insensitive), use:

Performance Considerations When Using REGEXP

While REGEXP is powerful, using it carelessly may impact database performance. Here are some best practices to keep queries efficient:

Use indexed columns whenever possible.
Avoid performing REGEXP operations on large datasets unless absolutely necessary.
Optimize queries by limiting the number of records to be checked.
If searching for specific words, consider full-text indexing as an alternative.

Conclusion

Other interesting article:

How ILIKE works in SQL? Best ILIKE examples