(opens in a new tab)

Postgres Regex: Match, Replace, Extract Examples

August 23, 2026 by Chat2DB Team

PostgreSQL ships one of the most complete regular-expression engines of any relational database. You can test a Postgres regex match with the ~ operator, rewrite text with regexp_replace, pull captured groups out with regexp_match, split strings into rows with regexp_split_to_table, and, since PostgreSQL 15, count, locate and validate with regexp_count, regexp_instr and regexp_like. This guide walks through all of it with runnable examples on a small sample table, explains the POSIX ARE dialect Postgres uses (it is close to Perl/PCRE but not identical), and finishes with the part most tutorials skip: how to keep regex queries fast with pg_trgm indexes. Everything is valid for PostgreSQL 14 through 17; version-specific functions are flagged.

Sample data

Run this first. You can paste it into Chat2DB, a free AI-powered SQL client (web version at https://app.chat2db.ai (opens in a new tab), desktop download at https://chat2db.ai/download (opens in a new tab)), or into psql.

CREATE TABLE contacts (
  id       serial PRIMARY KEY,
  name     text,
  email    text,
  phone    text,
  note     text
);
 
INSERT INTO contacts (name, email, phone, note) VALUES
  ('Alice Smith', 'alice.smith@example.com',  '+1 (415) 555-0101', 'Order #A-1001 shipped'),
  ('Bob Jones',   'BOB@Example.ORG',          '415-555-0199',      'order #B-22 refunded'),
  ('Carol Díaz',  'carol@mail.example.co.uk', '(020) 7946 0958',   'No order yet'),
  ('Dan Ross',    'not-an-email',             '555 0123',          'Orders #C-3 and #C-4 pending'),
  ('Eve Adams',   'eve+promo@example.com',    NULL,                'order#D-77 cancelled');

The four regex operators: , *, !, !*

Postgres regex matching is done with operators rather than a function (although regexp_like exists in PG15+). The pattern matches anywhere in the string unless you anchor it.

-- case-sensitive: only rows whose note contains lowercase 'order'
SELECT name FROM contacts WHERE note ~ 'order';
-- Bob Jones, Eve Adams
 
-- case-insensitive: 'Order', 'order', 'Orders' all match
SELECT name FROM contacts WHERE note ~* 'order';
-- Alice Smith, Bob Jones, Carol Díaz, Dan Ross, Eve Adams
 
-- rows whose email is NOT at example.com (case-insensitive)
SELECT name, email FROM contacts WHERE email !~* '@example\.com$';
-- Bob Jones, Carol Díaz, Dan Ross

Note the \. in the last pattern: an unescaped dot matches any character, so @example.com$ would also match @exampleXcom. A NULL operand always produces NULL, so WHERE phone !~ '^\+' silently drops Eve's row with the NULL phone; add OR phone IS NULL if you want it.

LIKE vs SIMILAR TO vs regex

Postgres gives you three pattern languages, and choosing the simplest one that works keeps queries readable and indexable.

• LIKE / ILIKE use only % (any sequence) and _ (any single character). They are the fastest and the easiest to index.
• SIMILAR TO is the SQL-standard hybrid: % and _ from LIKE plus |, *, +, ?, {m,n}, () and [...] from regex. It must match the entire string, as LIKE does. It is rarely worth using; it is implemented by rewriting to a regex internally, so it is no faster than ~, and its mixed syntax confuses readers.
• ~ (POSIX regex) is the most powerful and matches a substring unless anchored.

SELECT 'abc' LIKE 'a%'            AS like_ok,      -- true
       'abc' SIMILAR TO 'a%'      AS similar_ok,   -- true
       'abc' SIMILAR TO 'a'       AS similar_whole,-- false: must match whole string
       'abc' ~ 'a'                AS regex_sub,    -- true: substring match
       'abc' ~ '^a$'              AS regex_whole;  -- false

Rule of thumb: use LIKE when it can express the pattern, ~ otherwise, and skip SIMILAR TO.

POSIX ARE syntax in PostgreSQL

Postgres uses Henry Spencer's regex library in "Advanced Regular Expression" (ARE) mode. It covers almost everything you know from Perl, with a few things worth memorizing.

-- \m and \M: whole-word "order" only (note 'order#D-77' still matches: '#' is a non-word char)
SELECT name FROM contacts WHERE note ~* '\morder\M';
-- Alice Smith, Bob Jones, Carol Díaz, Eve Adams   (Dan has 'Orders', not 'order')
 
-- back-reference: find repeated words like "the the"
SELECT 'this is is a test' ~ '\m(\w+)\s+\1\M';   -- true

Greedy vs non-greedy: one pitfall

In Postgres, the greediness of the whole regex is decided by its first quantified atom, which differs from Perl, where each quantifier is independent. The classic demonstration from the PostgreSQL manual:

SELECT substring('XY1234Z' FROM 'Y*([0-9]{1,3})');    -- 123  (greedy overall)
SELECT substring('XY1234Z' FROM 'Y*?([0-9]{1,3})');   -- 1    (non-greedy overall: shortest total match wins)

The second query returns 1 even though [0-9]{1,3} is itself greedy, because the leading *? makes the entire match prefer the shortest string. Mixing greedy and non-greedy quantifiers in one pattern is therefore a common source of surprises; the practical advice is to write patterns that do not rely on mixed greediness, for example [^,]+ instead of .+?,.

Replacing text with regexp_replace

regexp_replace(source, pattern, replacement [, start [, N]] [, flags])

By default only the first match is replaced. Use the g flag for all matches and i for case-insensitive matching. Captured groups are referenced as \1 to \9 in the replacement, and \& is the whole match.

-- normalize phone numbers: strip everything except digits (g flag is essential)
SELECT phone, regexp_replace(phone, '\D', '', 'g') AS digits
FROM contacts WHERE phone IS NOT NULL;

-- without 'g' only the first non-digit is removed: '1 (415) 555-0101'
SELECT regexp_replace('+1 (415) 555-0101', '\D', '');
 
-- captured groups: swap "First Last" to "Last, First"
SELECT regexp_replace(name, '^(\w+)\s+(\w+)$', '\2, \1') FROM contacts;
-- "Smith, Alice", "Jones, Bob", ...
 
-- case-insensitive + global: mask every order code
SELECT regexp_replace(note, '#[A-Z]-\d+', '#***', 'gi') FROM contacts;
-- 'Orders #*** and #*** pending'
 
-- collapse runs of whitespace
SELECT regexp_replace('a    b  	c', '\s+', ' ', 'g');   -- 'a b c'

PostgreSQL 16 added the optional start and N arguments: regexp_replace(str, pattern, repl, 1, 2) replaces only the second occurrence. On PG14 and 15 use the four-argument form with flags only.

Extracting: regexp_match vs regexp_matches

regexp_match (PG10+) returns a text array with one element per capturing group (or the whole match if there are no groups) for the first match, or NULL if nothing matches. regexp_matches is a set-returning function that yields one row per match when called with g.

-- first order code per row, as a scalar: index the array with [1]
SELECT name, (regexp_match(note, '#([A-Z])-(\d+)'))[1] AS series,
             (regexp_match(note, '#([A-Z])-(\d+)'))[2] AS number
FROM contacts;

-- all order codes, one row each
SELECT c.name, m[1] AS code
FROM contacts c, regexp_matches(c.note, '#([A-Z]-\d+)', 'g') AS m;
-- Dan Ross appears twice: C-3 and C-4

The regexp_matches pitfall

Because regexp_matches returns a set, using it in a SELECT list for rows that do not match makes those rows disappear (zero rows returned means the row is dropped). Before PG10 people worked around this with a LEFT JOIN LATERAL; today the fix is simply to use regexp_match when you want at most one result per row:

-- BAD: Carol vanishes because her note has no order code
SELECT name, regexp_matches(note, '#([A-Z]-\d+)') FROM contacts;   -- 4 rows
 
-- GOOD: 5 rows, NULL for Carol
SELECT name, regexp_match(note, '#([A-Z]-\d+)') FROM contacts;

Also remember that without the g flag, regexp_matches returns at most one row, so regexp_matches(x, p) in a FROM clause with no flag is almost always a mistake if you wanted every match.

PG15+ convenience functions: regexp_substr, regexp_count, regexp_instr, regexp_like

PostgreSQL 15 added Oracle-style helpers that avoid array indexing.

SELECT note,
       regexp_count(note, '#[A-Z]-\d+')          AS n_codes,
       regexp_substr(note, '#[A-Z]-\d+')         AS first_code,
       regexp_substr(note, '#[A-Z]-\d+', 1, 2)   AS second_code,   -- start pos 1, 2nd occurrence
       regexp_instr(note, '#[A-Z]-\d+')          AS first_pos,
       regexp_like(note, 'order', 'i')           AS mentions_order
FROM contacts WHERE name = 'Dan Ross';

regexp_substr also accepts a subexpression number as its sixth argument, so regexp_substr(note, '#([A-Z])-(\d+)', 1, 1, '', 2) returns just 3. regexp_instr returns 0 when there is no match, not NULL. On PG14 you get the same results with (regexp_match(...))[1], array_length(regexp_split_to_array(...),1)-1 style tricks, or position().

Splitting strings with regex

regexp_split_to_table yields rows; regexp_split_to_array yields a text[]. Both accept flags.

-- split a CSV-ish list on comma OR semicolon with optional spaces
SELECT regexp_split_to_table('a, b;c ,  d', '\s*[,;]\s*');
-- a / b / c / d   (4 rows)
 
SELECT regexp_split_to_array('2026-08-23', '-');   -- {2026,08,23}
 
-- words in a sentence, case-insensitively dropping punctuation
SELECT w FROM regexp_split_to_table('Hello, World! Hello again.', '[^[:alnum:]]+') AS w
WHERE w <> '';

If you only split on a fixed delimiter, split_part, string_to_array or string_to_table (PG14) are simpler and a little cheaper; regex splitting is for variable separators.

SUBSTRING with a pattern

substring has two pattern forms. The POSIX form returns the part matched by the first parenthesized group, or the whole match if there are no groups:

SELECT substring(email FROM '@(.*)$') AS domain FROM contacts;
-- example.com, Example.ORG, mail.example.co.uk, NULL (Dan), example.com
 
SELECT substring('Order #A-1001 shipped' FROM '\d+');   -- 1001

The SQL-standard SIMILAR form needs an escape character and uses double-quote characters (") to mark the portion to return:

SELECT substring('Order #A-1001 shipped' SIMILAR '%#\"[A-Z]-[0-9]+\"%' ESCAPE '\');   -- A-1001

Most people find regexp_substr or regexp_match clearer than the SIMILAR form.

Practical extraction recipes

-- emails: a pragmatic (not RFC-complete) pattern, case-insensitive
SELECT name, email
FROM contacts
WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';
-- everyone except Dan Ross
 
-- pull all e-mail addresses out of free text
SELECT m[1]
FROM regexp_matches('contact a@x.io or b.c@y.org today', '([\w.+-]+@[\w-]+\.[\w.]+)', 'g') AS m;
 
-- US-style phone: exactly 10 digits after stripping formatting
SELECT name FROM contacts
WHERE regexp_replace(phone, '\D', '', 'g') ~ '^1?\d{10}$';
-- Alice Smith, Bob Jones
 
-- numeric IDs from mixed tokens
SELECT regexp_replace('INV-000123', '\D', '', 'g')::int;   -- 123
 
-- IPv4-looking strings (structure only, not range-checked)
SELECT '192.168.1.254' ~ '^(\d{1,3}\.){3}\d{1,3}$';   -- true

Validating input with CHECK constraints

A regex in a CHECK constraint gives you database-side validation that every client obeys.

ALTER TABLE contacts
  ADD CONSTRAINT contacts_email_format
  CHECK (email IS NULL OR email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$') NOT VALID;
 
-- existing bad rows are tolerated until you validate
SELECT name FROM contacts WHERE email !~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';  -- Dan Ross
UPDATE contacts SET email = NULL WHERE id = 4;
ALTER TABLE contacts VALIDATE CONSTRAINT contacts_email_format;
 
-- a SKU that must look like three letters, dash, four digits
ALTER TABLE contacts ADD COLUMN sku text
  CHECK (sku ~ '^[A-Z]{3}-\d{4}$');
INSERT INTO contacts (name, sku) VALUES ('X', 'abc-1234');  -- ERROR: violates check constraint

Using NOT VALID first and validating afterward avoids a full-table scan under an exclusive lock on a big table. Keep the patterns modest: a constraint that rejects legitimate addresses is worse than none.

Escaping backslashes and standard_conforming_strings

Since PostgreSQL 9.1, standard_conforming_strings defaults to on, so '\d+' is exactly the five characters \d+ and reaches the regex engine untouched. That is why the examples above use single backslashes. If you see old code with '\\d+' or E'\\d+', it was written for the legacy setting. To match a literal backslash you need '\\' in the pattern (two characters for the regex engine). If your driver or ORM doubles backslashes for you, check the actual SQL sent with auto_explain or the server log before debugging the regex itself.

Performance: indexes and regex

A plain B-tree index is useless for col ~ 'pattern'; the planner falls back to a sequential scan and evaluates the regex for every row. Three techniques help.

1. Anchored patterns with text_pattern_ops

If the pattern is anchored at the start and begins with a literal prefix (^abc), the planner can turn it into a range scan, provided the index uses the C collation or text_pattern_ops:

CREATE INDEX contacts_email_tpo ON contacts (email text_pattern_ops);
EXPLAIN SELECT * FROM contacts WHERE email ~ '^alice';
-- Index Scan using contacts_email_tpo ... Index Cond: ((email ~>=~ 'alice') AND (email ~<~ 'alicf'))
--                                         Filter: (email ~ '^alice')

This only works for ~, not ~*, and only for a literal prefix.

2. pg_trgm GIN index for unanchored regex (PG 9.3+)

The pg_trgm extension's GIN and GiST operator classes support ~, ~*, LIKE and ILIKE. Postgres extracts trigrams from the regex and uses the index to find candidate rows, then rechecks the real pattern.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- build a bigger table so the planner bothers with the index
CREATE TABLE logs AS
SELECT g AS id,
       'user' || (g % 5000) || '@' ||
       (ARRAY['example.com','mail.example.co.uk','test.org'])[1 + g % 3] ||
       ' ordered SKU-' || lpad((g % 9999)::text, 4, '0') AS line
FROM generate_series(1, 300000) g;
 
CREATE INDEX logs_line_trgm ON logs USING gin (line gin_trgm_ops);
ANALYZE logs;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM logs WHERE line ~ 'user4[0-9]2@mail\.example';

Illustrative plan shape (your numbers will differ):

Aggregate
  ->  Bitmap Heap Scan on logs
        Recheck Cond: (line ~ 'user4[0-9]2@mail\.example'::text)
        Rows Removed by Index Recheck: ...
        ->  Bitmap Index Scan on logs_line_trgm
              Index Cond: (line ~ 'user4[0-9]2@mail\.example'::text)

Without the index the same query is a Seq Scan over all 300k rows. The trigram index works for ~* too, because trigrams are extracted case-insensitively. It will not help when the pattern has no literal trigrams at all (for example '^\d+$' or '.*'); those still need a full scan. GIN indexes add write cost and can be large, so use them on columns you really search this way.

3. Move the regex out of the hot path

For validation-heavy workloads, a generated column or trigger that stores the extracted value (order_code text GENERATED ALWAYS AS (substring(note FROM '#([A-Z]-\d+)')) STORED) lets you put a normal B-tree index on the result and query with = instead of re-running the regex.

FAQ

Why does my pattern with \b not match word boundaries?

In PostgreSQL's ARE dialect, \b is the backspace character. Use \m (start of word), \M (end of word) or \y (either boundary). '\mcat\M' is the Postgres equivalent of Perl's \bcat\b.

regexp_replace only changed the first occurrence. Why?

Because regexp_replace without flags replaces one match. Pass 'g' as the fourth argument (regexp_replace(s, p, r, 'g')) to replace all matches, and 'gi' to also ignore case. On PG16+ you can instead give an explicit occurrence number.

Can I make ~* use an index?

Not with a B-tree. Create a pg_trgm GIN index (USING gin (col gin_trgm_ops)); it accelerates ~, ~*, LIKE and ILIKE as long as the pattern contains at least one run of three literal characters. For anchored prefix searches where case does not matter, an expression index on lower(col) text_pattern_ops combined with lower(col) ~ '^prefix' is another option.

Conclusion

PostgreSQL regex support is broad enough to replace most string-munging you would otherwise push into application code: ~ / ~* for filtering, regexp_replace with g and captured groups for rewriting, regexp_match for scalar extraction (and regexp_matches ... 'g' when you really want one row per match), regexp_split_to_table for tokenizing, and the PG15 additions regexp_substr, regexp_count, regexp_instr and regexp_like for Oracle-style convenience. Remember the dialect quirks (\m / \M instead of \b, whole-pattern greediness), keep standard_conforming_strings in mind when escaping, and reach for pg_trgm or anchored text_pattern_ops indexes before a regex query becomes a sequential-scan problem on a large table. If you want to experiment with the examples interactively, Chat2DB lets you run them against any PostgreSQL instance and inspect the EXPLAIN output side by side with the results.