LIKE and Wildcards

Sometimes you need to find data, but you don’t know the exact string you’re looking for. Maybe you want to find all users whose name starts with ‘A’, or all emails from a specific domain. The = operator won’t work here because it requires an exact, character-for-character match.

Instead, we use the LIKE operator in combination with Wildcards.

The % Wildcard (Any number of characters)

The percent sign % represents zero, one, or multiple characters.

LIKE 'A%': Matches any string that starts with ‘A’. (e.g., ‘Alice’, ‘Adam’, ‘A’).
LIKE '%son': Matches any string that ends with ‘son’. (e.g., ‘Johnson’, ‘Jackson’).
LIKE '%smith%': Matches any string that contains ‘smith’ anywhere inside it. (e.g., ‘John Smith’, ‘Smithson’, ‘Blacksmith’).

For example, to find all products that have the word “Wireless” in their name:

SELECT * FROM products WHERE name LIKE '%Wireless%';

The _ Wildcard (Exactly one character)

The underscore _ represents exactly one single character.

LIKE 'h_t': Matches ‘hat’, ‘hot’, ‘hit’, but not ‘hoot’ or ‘ht’.
LIKE 'C____': Matches any 5-letter word starting with ‘C’ (e.g., ‘Chair’, ‘Cloud’).

You can even combine them! LIKE '_a%' means: “The first character can be anything, the second character must be ‘a’, and after that, it can be anything.” (This would match ‘Batman’ and ‘Cat’).

[!WARNING] Using LIKE with a leading wildcard (e.g., '%smith') is notoriously slow on large tables because the database has to scan every single row; it cannot use an index. If performance is critical, try to structure queries to use LIKE 'smith%' instead.

Case Sensitivity

In PostgreSQL, LIKE is case-sensitive. LIKE 'A%' will not match ‘alice’. If you need a case-insensitive search in Postgres, you must use ILIKE instead. In MySQL and SQLite, LIKE is typically case-insensitive by default.

For our lab exercises, standard LIKE will behave case-insensitively, but keep this in mind for real-world databases!