Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 90 ยท Querying with PostgreSQL Features

Pattern Matching and Regular Expressions

Learn PostgreSQL pattern matching: LIKE and ILIKE, SIMILAR TO, POSIX regex operators ~ and ~*, regexp_replace, substring with regex, and pg_trgm similarity search.

Exact comparisons only go so far. Real questions are about shapes of text: titles that start with "The", phone numbers in a particular format, order codes with a number buried inside, names that are almost equal to what the user typed. SQL gives you several pattern tools, from simple wildcards up to full regular expressions. PostgreSQL has more of them than most databases.

This lesson walks up the ladder: LIKE and ILIKE, the rarely used SIMILAR TO, POSIX regular expressions with ~, and the regex functions that extract and rewrite text. It ends with a preview of pg_trgm, the extension behind fuzzy search. Our in-browser engine is SQLite, so only LIKE and GLOB run live. Everything that is specific to PostgreSQL is shown in read-only blocks. Use Full-Text Search when you want to find words and rank them; use this lesson when you need to match patterns.

The pattern toolbox

LIKE / ILIKE Two wildcards, % and _.
Regex ~ and ~* Full POSIX regular expressions.
Regex functions Extract, replace, split and count.
pg_trgm Fuzzy matching by similarity.
1 / 2

LIKE and its two wildcards

LIKE compares text with a pattern. Two characters are special: % matches any run of characters (including none), and _ matches exactly one character. Everything else matches itself. The match must cover the whole string, which is why patterns usually start or end with %.

SQL LIKE wildcards on book titles ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first query returns The Quiet Algorithm and The Salt Road, the second Paper Lanterns and Tango at Midnight, and the third A Map of Small Things and Rivers of Glass. These three shapes (prefix, fixed position, contains) cover most LIKE use.

Case sensitivity: an important difference

In PostgreSQL, LIKE is case-sensitive. 'The Salt Road' LIKE 'the%' is false. PostgreSQL adds ILIKE for case-insensitive matching, and the operators ~~ and ~~* are what LIKE and ILIKE are called underneath:

SQL
SELECT 'The Salt Road' LIKE  'the%';   -- false
SELECT 'The Salt Road' ILIKE 'the%';   -- true
SELECT 'The Salt Road' NOT ILIKE '%road';   -- false

SQLite behaves differently: its LIKE ignores case for ASCII letters, and there is no ILIKE. Be careful when you move a query between the two. The portable approach is to lower-case both sides, lower(title) LIKE lower('%rivers%'), which behaves the same everywhere (and in PostgreSQL pairs with an expression index on lower(title)). SQLite also has GLOB, which is case-sensitive:

SQL LIKE versus GLOB and case ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

For The Salt Road and The Quiet Algorithm the columns read 1, 0, 1: the pattern the% matches, the lowercase GLOB does not, the capitalized GLOB does. Northern Ledger is 0, 0, 0. The second query finds Rivers of Glass even though we typed the search in capitals.

Matching a literal % or _

To search for a real percent sign or underscore, put an escape character in front. PostgreSQL uses a backslash by default, and you can pick another with ESCAPE. SQLite has no default escape character, so you must always say ESCAPE, which is the portable form:

SQL Escaping wildcards

Press Run (or Ctrl + Enter) to see the result here.

The first query returns SAVEX10 and SAVE_10, because _ stands for exactly one character. The second returns only SAVE_10. The third returns only 50%OFF. If you build patterns from user input, escape %, _ and the escape character itself first; otherwise a search for 100% quietly matches far more than the user meant.

Indexes and LIKE

A plain B-tree index can speed up a prefix search such as LIKE 'abc%', but only if the index compares text in a way that fits: either the database uses the C collation, or you create the index with an operator class made for this, text_pattern_ops:

SQL
CREATE INDEX books_title_prefix ON books (title text_pattern_ops);

Patterns with a leading wildcard, LIKE '%abc%', cannot use that index at all. For those, PostgreSQL offers trigram indexes (see the end of this lesson). The details are in the Indexes chapter.

SIMILAR TO

SIMILAR TO mixes LIKE wildcards with a few regular-expression operators: | for alternatives, * and + for repetition, parentheses and [...] classes. It must match the whole string:

SQL
SELECT 'cat' SIMILAR TO '(cat|dog)s?';    -- true
SELECT 'dogs' SIMILAR TO '(cat|dog)s?';   -- true
SELECT 'bird' SIMILAR TO '(cat|dog)s?';   -- false

It is part of the SQL standard but almost nobody uses it. It has its own rules (. is a literal dot, not "any character") and is converted to a regex internally, so true regular expressions are clearer and more powerful. Treat it as something you may read in old code.

POSIX regular expressions: ~ and ~*

PostgreSQL's regex operators apply a POSIX regular expression to text:

Operator Meaning
~ matches, case-sensitive
~* matches, case-insensitive
!~ does not match, case-sensitive
!~* does not match, case-insensitive

Unlike LIKE, a regex matches anywhere in the string unless you anchor it with ^ (start) and $ (end):

SQL
SELECT 'order-4821' ~ '\d+';                 -- true  (contains digits)
SELECT 'order-4821' ~ '^\d+$';               -- false (not all digits)
SELECT 'AB1234' ~ '^[A-Z]{2}\d{4}$';         -- true  (two letters, four digits)
SELECT 'ab1234' ~* '^[A-Z]{2}\d{4}$';        -- true  (case-insensitive)
SELECT 'The Salt Road' ~ '\yRoad\y';         -- true  (\y marks a word boundary)

Useful pieces of the syntax: \d digit, \s space, \w word character, . any character, + one or more, * zero or more, ? optional, {n,m} repetition, (a|b) alternatives, and \y for a word boundary (PostgreSQL's own spelling; \b means backspace here). Because the pattern is a string literal, \d is written as-is in standard PostgreSQL strings, with standard_conforming_strings on (the default).

This makes regexes ideal for validation, for example as a CHECK constraint: CHECK (code ~ '^[A-Z]{2}\d{4}$').

SQLite has no ~ operator in our build. The nearest live tool is GLOB, with *, ? and character classes. It is case-sensitive, and [^...] negates a class:

SQL Validating a format with GLOB

Press Run (or Ctrl + Enter) to see the result here.

Only the first row has strict_format = 1. The other rows fail for different reasons: 555-01x4, (555) 0134 and 555-0134 ext 9 contain characters other than digits and a dash (has_odd_char = 1), while 5550134 has only digits but no dash. In PostgreSQL the strict check is one line: phone ~ '^\d{3}-\d{4}$'.

Extracting and rewriting with regex

PostgreSQL turns the regex engine into functions:

SQL
-- substring(text FROM pattern): returns the first capture group, or the whole match
SELECT substring('order #4821 shipped' FROM '#(\d+)');          -- '4821'

-- regexp_replace(text, pattern, replacement [, flags]): first match only, unless flag 'g'
SELECT regexp_replace('a  b   c', '\s+', ' ', 'g');             -- 'a b c'
SELECT regexp_replace('2024-05-17', '(\d+)-(\d+)-(\d+)', '\3/\2/\1');   -- '17/05/2024'

-- first match as an array of capture groups
SELECT regexp_match('v3.14.2', '(\d+)\.(\d+)\.(\d+)');          -- {3,14,2}

-- count matches (PostgreSQL 15+)
SELECT regexp_count('banana', 'an');                            -- 2

Notes on these:

  • regexp_replace replaces only the first match unless you pass the flag 'g'. Flags are letters in a string: g (global), i (ignore case), and others.
  • In the replacement text, \1, \2 and so on insert captured groups, and \& the whole match.
  • regexp_match returns the first match as an array, or NULL if nothing matches. Its sibling regexp_matches(..., 'g') returns every match, one row each, and is covered in generate_series and Set-Returning Functions.
  • PostgreSQL 15 added regexp_like, regexp_count, regexp_instr and regexp_substr, which mirror functions in other databases.

SQLite has no regex functions in our build, so cleaning text there means chaining replace() calls, which is clumsy but works for simple cases:

SQL
SELECT replace(replace(replace(replace(phone, '-', ''), ' ', ''), '(', ''), ')', '') AS cleaned
FROM contacts;

In PostgreSQL the same cleanup is regexp_replace(phone, '[^0-9]', '', 'g'), a single regex that strips everything except digits.

Regular expressions can be slow, and a badly written one on untrusted input can burn CPU. Do not let users supply their own patterns, avoid nested repetition such as (a+)+, and remember that ~ cannot use an ordinary B-tree index.

A preview of pg_trgm

Sometimes the pattern is "something like this". A trigram is a group of three consecutive characters, and the pg_trgm extension compares two strings by the trigrams they share. The more they share, the higher the similarity:

SQL
CREATE EXTENSION pg_trgm;

SELECT similarity('postgres', 'postgras');          -- a number from 0 to 1, high for near-matches
SELECT name FROM cities WHERE name % 'Barcelnoa';   -- % means "similar enough"
SELECT name FROM cities ORDER BY name <-> 'Barcelnoa' LIMIT 5;   -- closest first

Better still, a trigram index makes LIKE '%abc%', ILIKE and regex searches fast, which nothing else in the standard toolbox can:

SQL
CREATE INDEX cities_name_trgm ON cities USING gin (name gin_trgm_ops);

That is why "search as you type" and typo-tolerant lookups in PostgreSQL almost always involve pg_trgm. It is covered properly in pg_trgm, hstore, citext, pgcrypto and uuid-ossp.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL, what is the result of 'Hello' LIKE 'hello'?

    PostgreSQL's LIKE is case-sensitive. Use ILIKE (or lower() on both sides) for case-insensitive matches. SQLite behaves differently for ASCII letters.
  2. 2. Which wildcard matches exactly one character in LIKE?

    % matches any number of characters, _ matches one. (* and ? are GLOB wildcards in SQLite.)
  3. 3. What is the difference between LIKE '5%' and the regex ~ '5'?

    LIKE patterns cover the entire value, so you add % at the ends. A regex finds a match anywhere and needs ^ and $ to be strict.
  4. 4. What does regexp_replace('a b', '\s+', '_') do without the 'g' flag?

    Without 'g', regexp_replace stops after the first match. Pass 'g' to replace all of them.
  5. 5. Which tool makes '%abc%' style searches and typo-tolerant lookups fast in PostgreSQL?

    A text_pattern_ops B-tree helps only prefix searches. Trigram indexes support leading wildcards, ILIKE, regexes and similarity.

Summary

  • LIKE uses % (any run) and _ (one character) and must match the whole string; escape literal wildcards with ESCAPE, especially when the pattern comes from user input.
  • PostgreSQL LIKE is case-sensitive; ILIKE ignores case. SQLite's LIKE ignores ASCII case and has GLOB as its case-sensitive cousin; lower() on both sides is portable.
  • A prefix LIKE 'abc%' can use a B-tree with text_pattern_ops (or the C collation); a leading % cannot.
  • SIMILAR TO is a rarely used hybrid; prefer real regular expressions.
  • ~, ~*, !~, !~* match POSIX regexes anywhere in the text; anchor with ^ and $, and use \y for word boundaries.
  • substring(... FROM pattern), regexp_replace(..., 'g'), regexp_match and (15+) regexp_count extract and rewrite text.
  • pg_trgm adds similarity search and fast %abc% lookups with GIN trigram indexes.

Next: Window Functions in Depth, frames, named windows and statistics over ordered rows.