PostgreSQL Like

Pattern matching with LIKE

The LIKE operator compares a text value against a pattern rather than an exact string. It is used inside a WHERE clause whenever you need to find rows that contain, start with, or end with certain characters.

PostgreSQL supports two wildcard characters in LIKE patterns: the percent sign matches any sequence of zero or more characters, and the underscore matches exactly one character.

• % matches zero, one, or many characters
• _ matches exactly one character
• LIKE is case-sensitive by default
• ILIKE performs the same match but ignores case
• NOT LIKE excludes rows that match the pattern

SELECT name
FROM customers
WHERE name LIKE 'A%';

Matching anywhere in a string

Placing a percent sign on both sides of your search term finds the text anywhere within the column, which behaves like a 'contains' search.

SELECT email
FROM customers
WHERE email LIKE '%gmail%';

The underscore wildcard

The underscore is useful when you know the exact length of the part you don't care about, such as matching a product code where only one character varies.

SELECT product_code
FROM products
WHERE product_code ILIKE 'sku_001';