âš¡IntermediateAdvanced Functions
Regular Expressions
Match, extract, and transform text using POSIX regular expressions in PostgreSQL.
Overview
PostgreSQL supports POSIX regular expressions through operators ~ (case-sensitive match), ~* (case-insensitive), !~ (no match), !~* (case-insensitive no match), and functions REGEXP_REPLACE, REGEXP_MATCH, REGEXP_MATCHES. These are more powerful than LIKE patterns.
Use regex for complex validation (email format, phone patterns), extraction (pulling numbers from strings), and transformation (normalising inconsistent data formats).
Key Points
- •~ matches; !~ does not match; ~* and !~* are case-insensitive
- •REGEXP_MATCH(str, pattern) returns first match as TEXT[]
- •REGEXP_MATCHES(str, pattern, 'g') returns all matches
- •REGEXP_REPLACE(str, pattern, replacement, flags) replace with regex
- •^ anchors to start; $ to end; . matches any char; * zero or more; + one or more
- •(group) captures; [class] character class; \d digit; \w word char
Examples
Regex matching and extraction
-- Match emails (simple validation)
SELECT * FROM users WHERE email ~ '^[^@]+@[^@]+.[^@]+$';
-- Extract area code from phone number
SELECT REGEXP_MATCH('(555) 123-4567', '((d{3}))');
-- Returns: {555}
-- Replace all digits with X
SELECT REGEXP_REPLACE('Order 123 for $45.67', 'd', 'X', 'g');
-- Order XXX for $XX.XXExercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4