Regex Functions
intermediatepostgresql
2 min read
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.
Practice challengeGuided learning mode
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?
Initializing database...Each run starts from fresh sample data.
More Examples
Extract domain from email
Use regexp_matches to extract the domain.
Initializing database...Each run starts from fresh sample data.
Replace non-alphanumeric characters
Clean a string by removing special characters.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is ~ the same as LIKE?
No. ~ uses POSIX regular expressions. LIKE uses % and _ wildcards. Regex is more powerful but slower.
How do I do a case-insensitive regex match?
Use the ~* operator: WHERE column ~* 'pattern'. Or pass 'i' flag to functions: regexp_matches(str, pattern, 'i').