Regular Expressions in PostgreSQL

PostgreSQL supports full POSIX regular expressions for pattern matching — far more powerful than LIKE's %/_ wildcards.

The Match Operators

Operator	Meaning
~	Matches the regex (case-sensitive)
~*	Matches the regex (case-insensitive)
!~	Does NOT match (case-sensitive)
!~*	Does NOT match (case-insensitive)

SELECT * FROM employees WHERE email ~ '^[a-z]+@gmail\.com$';
-- case-sensitive: only lowercase usernames at gmail.com

SELECT * FROM employees WHERE name ~* '^r';
-- case-insensitive: names starting with r or R

SELECT * FROM employees WHERE phone !~ '^[0-9]{10}$';
-- phone numbers that are NOT exactly 10 digits

regexp_replace() — Find and Replace

SELECT regexp_replace(phone, '[^0-9]', '', 'g') FROM employees;
-- strips every non-digit character; the 'g' flag means "replace ALL matches", not just the first

SELECT regexp_replace(name, '(\w+) (\w+)', '\2 \1') FROM employees;
-- swaps first and last name using capture groups \1 and \2

regexp_matches() — Extract Matches

Returns an array of captured groups from the first match.

SELECT regexp_matches(email, '^(.+)@(.+)$') FROM employees;
-- {username, domain} as a text array

SELECT (regexp_matches(email, '^(.+)@(.+)$'))[1] AS username FROM employees;

regexp_split_to_table() — Split on a Pattern, One Row Per Piece

SELECT regexp_split_to_table('one,two;three four', '[,; ]+');
-- returns 'one', 'two', 'three', 'four' as separate rows

Common Errors & Troubleshooting

• Forgetting ~* and getting zero matches due to case — ~ is case-sensitive by default; if the data's casing is inconsistent, use ~* instead.
• regexp_replace() only replacing the first match — by default it replaces just the first occurrence; add the 'g' flag as the fourth argument to replace every match in the string.
• Special regex characters in the pattern behaving unexpectedly — characters like ., (, ), and + are regex metacharacters; if you need to match them literally (e.g. a literal dot in an email), escape them with a backslash (\.).

Key Takeaways

• ~/~* test whether a column matches a regex pattern; use ~* when case shouldn't matter.
• regexp_replace() supports capture groups (\1, \2, ...) for rearranging matched text, not just deleting it.
• Add the 'g' flag to regexp_replace() when you want every match replaced, not just the first one found.