Using Regular Expressions in SQL

The LIKE operator is great for simple patterns (keeps starting with 'A', ends with 'z'). But what if you need to:

• Find email addresses in a mess of text?
• Validate phone numbers?
• Match strings with "3 digits, a dash, then 4 letters"?

For these power-user tasks, you need Regular Expressions (Regex).

A Real Business Scenario: Validating Imported Contact Data

A common SQL regex job is not "find a clever pattern for fun". It is "stop bad records from slipping into a report or workflow".

Imagine you import leads from:

• marketing forms
• CSV uploads from sales
• old CRM exports

Now you need to answer practical questions:

• which rows have malformed emails?
• which notes contain something that looks like a phone number?
• which identifiers follow the expected invoice or ticket format?

Regex helps when the business rule is shape-based. You do not just care whether a string contains text. You care whether it matches a structural pattern closely enough to trust downstream.

What is Regex?

Regex is a sequence of characters that specifies a search pattern. It's available in almost all programming languages, and yes—in SQL too!

In SQL Boy (and SQLite), we use the REGEXP operator.

Note: Standard SQLite doesn't strictly implement REGEXP by default, but we've added a custom function to the SQL Boy playground so you can use it right here!

Basic Syntax Cheat Sheet

Symbol	Description	Example	Matches
.	Any single character	h.t	hat, hot, hit
^	Start of string	^Hello	Hello World
$	End of string	World$	Hello World
[abc]	Any one character in brackets	[bcr]at	bat, cat, rat
[0-9]	Any digit	User[0-9]	User1, User9
*	Zero or more repetitions	go*d	gd, god, good
+	One or more repetitions	go+d	god, good

Interactive Example: Validating Emails

Let's find all invalid email addresses in our users table. A valid email should roughly follow: text @ text . text.

A Common Mistake: Using Regex When LIKE or Equality Would Do

Regex is powerful, but it is easy to overuse.

For example, if you only need rows where a code starts with INV-, this:

WHERE code REGEXP '^INV-'

is usually less clear and often less index-friendly than:

WHERE code LIKE 'INV-%'

That matters in production queries. Regex should earn its complexity. If the real rule is prefix, suffix, or simple containment, use the simpler operator first.

Finding Patterns: Phone Numbers

Imagine you have a text field where users can dump any contact info. You want to extract rows that look like they contain a US phone number (3 digits - 3 digits - 4 digits).

REGEXP vs LIKE

Feature	LIKE	REGEXP
Simplicity	High (just % and _)	Low (cryptic syntax)
Power	Basic prefix/suffix	Unlimited pattern matching
Performance	Fast (can use indexes)	Slower (scans full strings)
Portability	Universal	Varies (MySQL REGEXP, Postgres ~, Oracle REGEXP_LIKE)

Boundary and Performance Notes

Regex filtering is expressive, but it comes with tradeoffs:

• many regex predicates require full string scans and cannot use ordinary b-tree indexes well
• different databases use different operators, escaping rules, and regex engines
• a pattern that is "good enough" for validation may still allow false positives or false negatives
• complex user-supplied regex can become difficult to review and expensive to execute at scale

That means regex is often best for:

• cleanup workflows
• back-office validation
• ETL quality checks
• targeted analysis over a limited subset of rows

It is less attractive as the default filter on a hot transactional path unless you have profiled it and accepted the cost.

When NOT to Use Regex in SQL

Do not force regex into SQL when another layer fits better.

Examples:

• strict email validation where the application should validate input before it ever reaches the database
• large-scale full-text or fuzzy search that belongs in search infrastructure, not hand-written regex scans
• complicated parsing logic that becomes unreadable and unmaintainable once encoded in one giant pattern

Regex is a sharp tool. Use it when pattern matching is the clearest expression of the rule, not when it merely feels powerful.

Official References

• PostgreSQL pattern matching documentation for LIKE, SQL-standard pattern matching, and POSIX regex operators.
• MySQL regular expression reference for another major-engine view of regex syntax and behavior.
• SQLite expression documentation for SQLite operator behavior and the caveat that REGEXP requires an application-defined function.

Best Practices

1. Don't overcomplicate: Use LIKE if you just need "starts with" or "contains".
2. Test your patterns: Regex is notorious for "false positives". Test with diverse data.
3. Performance: Avoid running complex Regex on millions of rows in real-time. Do it in background jobs or ETL processes.

Related Articles

• Understanding Database Indexes: The Key to Performance for why prefix patterns can be fast while broad text scans usually are not.
• Dynamic SQL Best Practices for the cases where regex-based filters become part of generated query logic.
• Preventing SQL Injection for the security boundary when search patterns originate from user input.

Conclusion

Regex gives you superpowers for text data. It unlocks the ability to clean messy inputs, validate formats, and extract hidden insights that standard SQL functions can't touch.

Start simple with ^ (starts with) and $ (ends with), and build up to complex validation patterns!