PostgreSQL Regex: Advanced Pattern Matching
In PostgreSQL, regex (regular expressions) enables powerful pattern matching for string data, useful for filtering, searching, and manipulating text. Regex allows you to identify specific patterns within text fields, making it ideal for data validation, cleaning, and advanced searches within your database. PostgreSQL supports regex-based functions like ~ (match), ~* (case-insensitive match), !~ (does not match), and !~* (case-insensitive does not match), offering flexibility for handling various matching needs.
Syntax:
1. Pattern Matching:
column_name ~ 'regex_pattern'
2. Case-Insensitive Matching:
column_name ~* 'regex_pattern'
3. Negation:
!~ for "does not match" (case-sensitive). !~* for "does not match" (case-insensitive).
Example 1: Basic Regex Pattern Matching
This example searches for rows in the users table where the email column contains email addresses from a specific domain (e.g., @gmail.com).
Code:
-- Find all users with a Gmail email address
SELECT * FROM users
WHERE email ~ '@gmail\.com$';
Explanation:
Example 2: Case-Insensitive Regex Matching
This example retrieves data for users whose first names start with "a" or "A".
Code:
-- Find users whose first name starts with "a" or "A"
SELECT * FROM users
WHERE first_name ~* '^a';
Explanation:
Example 3: Regex to Exclude Patterns
In this case, we select records where the username column does not contain any digits.
Code:
-- Select users with usernames that have no digits
SELECT * FROM users
WHERE username !~ '[0-9]';
Explanation:
Example 4: Using Regex for Validation
Validate phone numbers in the contacts table to ensure they follow a specific format, e.g., (XXX) XXX-XXXX.
Code:
-- Select valid phone numbers in the format (XXX) XXX-XXXX
SELECT * FROM contacts
WHERE phone ~ '^\(\d{3}\) \d{3}-\d{4}$';
Explanation:
Additional PostgreSQL Regex Functions:
1. regexp_replace(): Replaces occurrences of a regex pattern within a string.
Code:
SELECT regexp_replace('abc123', '[0-9]', '', 'g');
-- Result: 'abc'
2. regexp_matches(): Returns an array of text matching a regex pattern.
Code:
SELECT regexp_matches('hello world', '\w+');
-- Result: {hello, world}
3. regexp_split_to_table(): Splits a string based on a regex pattern into table rows.
Code:
SELECT regexp_split_to_table('apple,banana,cherry', ',');
-- Result: apple, banana, cherry
Summary:
PostgreSQL's regex support allows for sophisticated string pattern matching and manipulation, ideal for data validation and complex text-based queries. By leveraging regex functions, you can efficiently filter, search, and clean data to meet various application requirements.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4