Using Regular Expressions
1. Pattern Matching
| Operator | Behavior |
|---|---|
| ~ | Case-sensitive match |
| ~* | Case-insensitive match |
| !~ / !~* | Negated |
| SIMILAR TO | SQL-standard, like LIKE++ |
SELECT * FROM logs WHERE message ~* '\merror\M';2. Using REGEXP_MATCH Function
SELECT regexp_match('user@example.com', '^([^@]+)@(.+)
3. Using REGEXP_MATCHES Function
SELECT regexp_matches('aaa-bbb-ccc', '\w+', 'g'); -- 3 rows
Flag Meaning g Global (all matches) i Case-insensitive n Newline-sensitive x Ignore whitespace in pattern
4. Using REGEXP_REPLACE Function
SELECT regexp_replace('Phone: 555-1234', '\d', '*', 'g');
5. Using REGEXP_SPLIT_TO_TABLE Function
SELECT regexp_split_to_table('one,two;three', '[,;]') AS token;
6. Using REGEXP_SPLIT_TO_ARRAY Function
SELECT regexp_split_to_array('one,two;three', '[,;]'); -- {one,two,three}
7. Using Capture Groups
SELECT (regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)
8. Using Flags
Flag Effect i Case-insensitive g Global s . matches newline m ^/$ per line
9. Using Character Classes
Class Matches \d / [[:digit:]] Digit \w / [[:alnum:]_] Word char \s / [[:space:]] Whitespace [^...] Negated class
10. Using Anchors
Anchor Match ^ String start (or line with 'm') $ String end \m / \M Word start / end \y Word boundary
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)
12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)
13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'
Note: regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.
);
-- {user,example.com}
3. Using REGEXP_MATCHES Function
SELECT regexp_matches('aaa-bbb-ccc', '\w+', 'g'); -- 3 rows
Flag Meaning g Global (all matches) i Case-insensitive n Newline-sensitive x Ignore whitespace in pattern
4. Using REGEXP_REPLACE Function
SELECT regexp_replace('Phone: 555-1234', '\d', '*', 'g');
5. Using REGEXP_SPLIT_TO_TABLE Function
SELECT regexp_split_to_table('one,two;three', '[,;]') AS token;
6. Using REGEXP_SPLIT_TO_ARRAY Function
SELECT regexp_split_to_array('one,two;three', '[,;]'); -- {one,two,three}
7. Using Capture Groups
SELECT (regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[1] AS first,
(regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[3] AS age;
8. Using Flags
Flag Effect i Case-insensitive g Global s . matches newline m ^/$ per line
9. Using Character Classes
Class Matches \d / [[:digit:]] Digit \w / [[:alnum:]_] Word char \s / [[:space:]] Whitespace [^...] Negated class
10. Using Anchors
Anchor Match ^ String start (or line with 'm') $ String end \m / \M Word start / end \y Word boundary
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)
12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)
13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'
Note: regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.
))[1] AS first,
(regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)
8. Using Flags
Flag Effect i Case-insensitive g Global s . matches newline m ^/$ per line
9. Using Character Classes
Class Matches \d / [[:digit:]] Digit \w / [[:alnum:]_] Word char \s / [[:space:]] Whitespace [^...] Negated class
10. Using Anchors
Anchor Match ^ String start (or line with 'm') $ String end \m / \M Word start / end \y Word boundary
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)
12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)
13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'
Note: regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.
);
-- {user,example.com}3. Using REGEXP_MATCHES Function
SELECT regexp_matches('aaa-bbb-ccc', '\w+', 'g'); -- 3 rows| Flag | Meaning |
|---|---|
| g | Global (all matches) |
| i | Case-insensitive |
| n | Newline-sensitive |
| x | Ignore whitespace in pattern |
4. Using REGEXP_REPLACE Function
SELECT regexp_replace('Phone: 555-1234', '\d', '*', 'g');5. Using REGEXP_SPLIT_TO_TABLE Function
SELECT regexp_split_to_table('one,two;three', '[,;]') AS token;6. Using REGEXP_SPLIT_TO_ARRAY Function
SELECT regexp_split_to_array('one,two;three', '[,;]'); -- {one,two,three}7. Using Capture Groups
SELECT (regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[1] AS first,
(regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[3] AS age;8. Using Flags
| Flag | Effect |
|---|---|
| i | Case-insensitive |
| g | Global |
| s | . matches newline |
| m | ^/$ per line |
9. Using Character Classes
| Class | Matches |
|---|---|
| \d / [[:digit:]] | Digit |
| \w / [[:alnum:]_] | Word char |
| \s / [[:space:]] | Whitespace |
| [^...] | Negated class |
10. Using Anchors
| Anchor | Match |
|---|---|
| ^ | String start (or line with 'm') |
| $ | String end |
| \m / \M | Word start / end |
| \y | Word boundary |
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'Note:
))[3] AS age;
regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.8. Using Flags
| Flag | Effect |
|---|---|
| i | Case-insensitive |
| g | Global |
| s | . matches newline |
| m | ^/$ per line |
9. Using Character Classes
| Class | Matches |
|---|---|
| \d / [[:digit:]] | Digit |
| \w / [[:alnum:]_] | Word char |
| \s / [[:space:]] | Whitespace |
| [^...] | Negated class |
10. Using Anchors
| Anchor | Match |
|---|---|
| ^ | String start (or line with 'm') |
| $ | String end |
| \m / \M | Word start / end |
| \y | Word boundary |
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'Note:
);
-- {user,example.com}
regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.3. Using REGEXP_MATCHES Function
SELECT regexp_matches('aaa-bbb-ccc', '\w+', 'g'); -- 3 rows| Flag | Meaning |
|---|---|
| g | Global (all matches) |
| i | Case-insensitive |
| n | Newline-sensitive |
| x | Ignore whitespace in pattern |
4. Using REGEXP_REPLACE Function
SELECT regexp_replace('Phone: 555-1234', '\d', '*', 'g');5. Using REGEXP_SPLIT_TO_TABLE Function
SELECT regexp_split_to_table('one,two;three', '[,;]') AS token;6. Using REGEXP_SPLIT_TO_ARRAY Function
SELECT regexp_split_to_array('one,two;three', '[,;]'); -- {one,two,three}7. Using Capture Groups
SELECT (regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[1] AS first,
(regexp_match('John Doe, 42', '^(\w+) (\w+), (\d+)$'))[3] AS age;8. Using Flags
| Flag | Effect |
|---|---|
| i | Case-insensitive |
| g | Global |
| s | . matches newline |
| m | ^/$ per line |
9. Using Character Classes
| Class | Matches |
|---|---|
| \d / [[:digit:]] | Digit |
| \w / [[:alnum:]_] | Word char |
| \s / [[:space:]] | Whitespace |
| [^...] | Negated class |
10. Using Anchors
| Anchor | Match |
|---|---|
| ^ | String start (or line with 'm') |
| $ | String end |
| \m / \M | Word start / end |
| \y | Word boundary |
11. Using REGEXP_COUNT Function
SELECT regexp_count('aaa bbb aaa', 'aaa'); -- 2 (PG 15+)12. Using REGEXP_INSTR Function
SELECT regexp_instr('foo bar baz', 'b\w+', 1, 2); -- 9 (PG 15+)13. Using REGEXP_SUBSTR Function
SELECT regexp_substr('id=42 type=admin', 'id=(\d+)', 1, 1, '', 1); -- '42'Note:
regexp_count, regexp_instr, regexp_substr, regexp_like added in PG 15 for Oracle compatibility.