Menu

PostgreSQL LIKE, ILIKE and Regular Expressions

LIKE matches text against a pattern with % and _, ILIKE does the same ignoring case, and the ~ operators match POSIX regular expressions.

This page includes runnable editors. Edit, run, and see output instantly.

LIKE compares text with a pattern in which % matches any run of characters and _ matches exactly one, so email LIKE '%@gmail.com' finds Gmail addresses. LIKE is case-sensitive. ILIKE is the same match ignoring case, and ~ and ~* match POSIX regular expressions.

It returns Asha and Bella: ILIKE ignores the capital letters in Bella's address, and Chen's .co does not match the ending.

Match with % and _

The whole string must match the pattern, so a pattern without % acts like =. NOT LIKE and NOT ILIKE invert the match.

A-___ matches A-100 but not A-2000, since each _ stands for exactly one character. The lowercase a-300 passes only the ILIKE test.

Search for a literal % or _

A backslash escapes the next character in a pattern, so '%50\%%' finds text containing 50%. When backslashes are part of the data, pick another escape character with ESCAPE:

SAVE\_% matches SAVE_10 only. Without the escape, _ would match the 1 in SAVE10 too.

Regular expressions with ~

The regex operators are ~ (matches), ~* (matches, ignoring case), !~ and !~* (does not match). A regex matches anywhere in the string unless you anchor it with ^ and $:

Only 555-0101 matches the dashed format. regexp_replace with the g flag removes every non-digit, so (555) 0199 becomes 5550199 and call me becomes an empty string.

Extract and count matches

Other regex functions return what matched instead of true or false:

FunctionReturns
substring(s from 'pattern')The first match, or the first parenthesized group
regexp_match(s, 'pattern')The groups of the first match as a text[]
regexp_matches(s, 'pattern', 'g')One row per match
regexp_count(s, 'pattern')The number of matches (since PostgreSQL 15)
regexp_substr(s, 'pattern')The first match (since PostgreSQL 15)
regexp_like(s, 'pattern')Same as ~, a function (since PostgreSQL 15)
regexp_split_to_table(s, 'pattern')One row per piece between matches

substring('Order 4521 shipped' from '\d+') returns 4521. The pattern matching chapter of the manual lists the flags and regex syntax.

Make pattern searches use an index

A B-tree index can serve a pattern anchored at the start, such as LIKE 'abc%', when the database uses the C collation or the index is built with text_pattern_ops:

CREATE INDEX users_email_prefix ON users (email text_pattern_ops);

A pattern that starts with %, ILIKE, and most regexes cannot use a B-tree. For those, the pg_trgm extension adds trigram indexes that serve LIKE, ILIKE, ~ and ~*:

CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);

For case-insensitive equality rather than patterns, an index on lower(email), queried with lower(email) = 'ben@work.io', is the simpler choice. The string functions page covers lower, position and split_part.

Frequently Asked Questions

How do I check if a string contains a substring in PostgreSQL?

Use col LIKE '%text%', or col ILIKE '%text%' to ignore case. position('text' in col) > 0 and strpos(col, 'text') > 0 work too and need no escaping of % or _.

Is LIKE case-sensitive in PostgreSQL?

Yes. 'Apple' LIKE 'a%' is false. Use ILIKE, compare lower(col) LIKE 'a%', or use the case-insensitive regex operator ~*.

How do I match several patterns at once?

Combine LIKE ANY with an array: name LIKE ANY (ARRAY['a%', 'b%']) is true when any pattern matches, and LIKE ALL requires every one. A regex alternation such as name ~ '^(a|b)' does the same.

What is SIMILAR TO in PostgreSQL?

A SQL-standard mix of LIKE and regular expressions: it uses % and _ plus |, *, + and groups, and must match the whole string. LIKE or the ~ operators are usually clearer.

Coddy programming languages illustration

Learn to code with Coddy

Get started