SQL Regex: Syntax, Usage, and Examples

The SQL regex feature allows you to use regular expressions to search, match, and filter text within your SQL queries. While not part of standard SQL, many databases support regex operations to enable more advanced string matching and pattern detection.

How to Use Regex in SQL

Use SQL regex by calling specific functions or operators provided by your database system. The most common include REGEXP, REGEXP_LIKE, or ~ depending on the SQL dialect.

In MySQL:

SELECT * FROM users WHERE username REGEXP '^[A-Za-z0-9_]+$';

In PostgreSQL:

SELECT * FROM products WHERE name ~* 'shirt$';

In Oracle:

SELECT * FROM employees WHERE REGEXP_LIKE(email, '.*@example\.com$');

Use regex in SQL to define complex string patterns directly within your query.

When to Use SQL Regex

Regex in SQL becomes useful when traditional string operators like LIKE or IN don’t meet your needs.

Filter by Complex Patterns

Use regex when you need to match strings that follow a particular structure. For example, filter usernames that contain only alphanumeric characters and underscores.

Validate Input Formats

Use SQL regex to validate strings like email addresses, phone numbers, or ZIP codes. This can help you flag or reject malformed data at the query level.

Extract or Compare Text Segments

Use regular expression SQL queries to find rows that include specific patterns like digits, whitespace, punctuation, or exact repetitions. This helps with data cleansing and analysis.

Examples of Regex in SQL Queries

Here are practical examples that show how to apply regex in SQL query filters and logic.

Match Alphanumeric Usernames

SELECT username FROM users
WHERE username REGEXP '^[A-Za-z0-9_]+$';

Use this to select usernames that contain only letters, numbers, and underscores.

Find Products That End With a Keyword

SELECT name FROM products
WHERE name REGEXP 'T-shirt$';

Use this pattern to find product names that end with "T-shirt."

Match Emails from a Specific Domain

SELECT email FROM employees
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@example\.com$');

This filters for emails ending in @example.com, enforcing stricter format rules than LIKE.

Validate Phone Number Format

SELECT phone_number FROM customers
WHERE phone_number ~ '^\d{3}-\d{3}-\d{4}$';

Use this to find phone numbers that match the pattern 123-456-7890.

Extract Rows With Multiple Digits

SELECT description FROM orders
WHERE description REGEXP '[0-9]{2,}';

Use this to find rows where the description includes at least two consecutive digits.

Learn More About SQL Regular Expressions

Regex SQL Syntax Overview

While regex patterns follow similar rules across languages, SQL implementations often change slightly between systems:

• MySQL uses REGEXP and NOT REGEXP. Case sensitivity depends on collation.
• PostgreSQL uses ~ (case-sensitive), ~* (case-insensitive), !~ (not match), and !~* (not match case-insensitive).
• Oracle provides REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR.
• SQL Server doesn’t support regex natively but you can simulate it using CLR integrations or external tools.

Always test patterns in your specific environment since behavior can vary.

Regex Character Classes

Here are some common classes and what they do:

• . matches any character except newline.
• [abc] matches one character in the set.
• [^abc] matches any character not in the set.
• \d matches any digit.
• \w matches a word character (alphanumeric or underscore).
• \s matches any whitespace.

Use these to build compact, expressive patterns.

Anchors and Quantifiers

• ^ anchors to the beginning of the string.
• $ anchors to the end.
• matches zero or more of the previous element.
• + matches one or more.
• {n} matches exactly n times.
• {n,} matches at least n times.
• {n,m} matches between n and m times.

Anchors and quantifiers help you control how patterns behave.

Regex in SQL Query Performance

Using regex in SQL can be powerful, but it may also affect performance. Avoid applying regex to columns without indexes or very large text fields unless absolutely necessary.

Use regex filters sparingly and only when simpler operators like LIKE, IN, or equality don’t meet the requirement. Test with EXPLAIN plans if performance is a concern.

Combining Regex With SQL Logic

You can combine regex SQL filters with standard SQL clauses:

SELECT id, description
FROM logs
WHERE description REGEXP 'error|fail|exception'
AND timestamp >= '2024-01-01';

Use logical operators to combine regex with date ranges, status fields, or categories.

Replacing Text With Regex

Some databases let you use regex for replacement:

-- Oracle
SELECT REGEXP_REPLACE(name, '[^A-Za-z0-9]', '', 1, 0) AS clean_name
FROM users;

Use this when you want to strip punctuation, whitespace, or other unwanted characters from strings.

Regex Alternatives

If regex is unsupported or overkill for your use case, consider these alternatives:

• LIKE for simple wildcard matches (% and _).
• INSTR or CHARINDEX for substring searches.
• SUBSTRING for position-based matching.
• Stored procedures or UDFs to encapsulate regex-like logic.

Choose the simplest tool that meets your needs.

Use SQL regex features when you need advanced pattern matching and validation that goes beyond basic string filters. Whether you're validating email addresses, extracting digits, or filtering logs with specific keywords, regex in SQL provides a flexible and powerful way to work with text-based data. Just keep an eye on performance, and don’t forget to escape special characters when needed.