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:
| Function | Returns |
|---|---|
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.