SQL LIKE Operator Explained: Wildcards for Beginners

The SQL LIKE operator lets you search for text that matches a pattern, instead of an exact value. You use it inside a WHERE clause with two special symbols, called wildcards: % (any number of characters) and _ (exactly one character). For example, WHERE name LIKE 'J%' finds every name that starts with 'J'. This guide explains LIKE and wildcards from scratch, with small examples you can run yourself.

What is the SQL LIKE operator?

Normally, when you filter rows with WHERE, you compare a column to an exact value. For example, WHERE country = 'India' only matches rows where the country column is exactly 'India'. But what if you don't know the exact value? What if you just want every customer whose email ends in '@gmail.com', or every product whose name contains the word 'phone'? That's what LIKE is for. LIKE compares a column to a pattern instead of an exact value, and returns every row that fits the pattern.

If you haven't used WHERE before, it's worth reading our guide on SQL SELECT and WHERE first, since LIKE is just a more flexible way of writing a WHERE condition.

The basic syntax

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

Line by line: SELECT picks the columns you want to see. FROM tells the database which table to read. WHERE column_name LIKE 'pattern' is the new part — instead of an exact value, you write a pattern in quotes, and the database returns every row where the column matches that pattern.

The % wildcard: match any number of characters

The % symbol stands in for zero, one, or many characters. Think of it as a blank space that can be filled with anything, including nothing at all. Suppose you have a customers table with a name column.

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

This finds every name that starts with 'J', followed by anything. 'John', 'Jane', 'Jim', and even just 'J' on its own would all match, because % can represent zero or more characters after the J.

SELECT email
FROM customers
WHERE email LIKE '%@gmail.com';

Here the % comes first, so this matches any email that ends in '@gmail.com', no matter what comes before the @ sign. Putting % at the start means "anything can come before this text".

SELECT name
FROM products
WHERE name LIKE '%phone%';

With % on both sides, this matches any product name that contains the word 'phone' anywhere inside it, such as 'iPhone 15', 'Smartphone Case', or 'Phone Charger'. This is the pattern you'll use most often when you want a simple "contains" search.

The _ wildcard: match exactly one character

The underscore _ is stricter than %. It stands for exactly one character, no more and no fewer. This is useful when you know the length of the text but not every letter.

SELECT product_code
FROM products
WHERE product_code LIKE 'A_1';

This matches any three-character code that starts with 'A', has any single character in the middle, and ends with '1' — so 'AB1' and 'AX1' both match, but 'A1' (too short) and 'ABC1' (too long) do not.

% vs _ at a glance

Case sensitivity: LIKE vs ILIKE

Whether LIKE cares about uppercase and lowercase letters depends on the database. In MySQL, LIKE is usually case-insensitive by default, so 'john' matches 'John'. In PostgreSQL, LIKE is case-sensitive, so 'john' will NOT match 'John'. PostgreSQL gives you a separate operator, ILIKE, that ignores case.

-- PostgreSQL: case-sensitive
SELECT name FROM customers WHERE name LIKE 'john%';

-- PostgreSQL: case-insensitive
SELECT name FROM customers WHERE name ILIKE 'john%';

The first query only matches names starting with a lowercase 'john'. The second query, using ILIKE instead of LIKE, matches 'John', 'JOHN', and 'john' all the same way. If you're on PostgreSQL and unsure whether casing matters in your data, ILIKE is the safer choice.

Combining LIKE with AND, OR, and NOT LIKE

You can combine LIKE with other conditions, just like any WHERE clause. This lets you build more specific searches.

SELECT name, city
FROM customers
WHERE name LIKE 'A%'
  AND city = 'Mumbai';

This returns customers whose name starts with 'A' AND whose city is exactly 'Mumbai'. Both conditions must be true for a row to be included.

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

This returns customers whose name starts with either 'A' OR 'B'. With OR, only one of the conditions needs to be true.

SELECT name
FROM customers
WHERE name NOT LIKE '%test%';

NOT LIKE reverses the match. This query returns every customer whose name does NOT contain the word 'test' — useful for filtering out placeholder or fake test accounts from real results.

Common mistakes beginners make

• Forgetting the quotes: the pattern must be in single quotes, like 'A%', not A%.
• Using * instead of %. SQL wildcards are % and _, not the * you may have seen in file searches.
• Assuming LIKE is always case-insensitive. It depends on the database — check with a quick test query first.
• Using LIKE '%value%' on huge tables without an index and being surprised it's slow. A leading % (like '%value') usually can't use a normal index, so it scans the whole table.
• Mixing up % and _. Remember: % is flexible (any number of characters), _ is strict (exactly one).

Practice example

Imagine a table called employees with a column full_name. Try to guess what each query below returns before reading the answer.

SELECT full_name FROM employees WHERE full_name LIKE '%an';

This matches any name ending in 'an', such as 'Susan' or 'Jordan'. The % at the start means anything can come before 'an', but the string must end exactly with those two letters.