Regex Functions
What are Regex Functions?
PostgreSQL supports POSIX regular expressions for powerful pattern matching. The ~ operator matches patterns, and functions like regexp_matches, regexp_replace, and regexp_split_to_table provide advanced text processing.
Operators and Functions
-- Match (case-sensitive)
column ~ 'pattern'
-- Match (case-insensitive)
column ~* 'pattern'
-- Not match
column !~ 'pattern'
-- Extract matches
regexp_matches(string, pattern [, flags])
-- Replace
regexp_replace(string, pattern, replacement [, flags])
-- Split
regexp_split_to_table(string, pattern)When to Use
- Complex pattern matching beyond LIKE/SIMILAR TO
- Extracting parts of strings (phone numbers, emails, URLs)
- Data cleaning and normalization
- Splitting delimited text
Key Points
- ~ Operator — Case-sensitive match. ~* for case-insensitive.
- POSIX Syntax — Uses standard regex: \d, \w, [a-z], ^, $, +, *, etc.
- regexp_matches — Returns text[] arrays of captured groups.
- regexp_replace — Replaces matched text. Use 'g' flag for global replacement.
- Performance — Regex is slower than LIKE. Use only when simpler operators are insufficient.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Regex Functions Challenge
Write a query that find students with valid-looking email addresses.
Expected result
Students whose email matches a basic email regex pattern.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Regex Functions?
Press Run to execute the query once the engine is ready.
More Examples
Extract domain from email
Use regexp_matches to extract the domain.
Press Run to execute the query once the engine is ready.
Replace non-alphanumeric characters
Clean a string by removing special characters.
Press Run to execute the query once the engine is ready.