SQL Like

What the LIKE operator does

Most WHERE conditions test for exact equality, but real questions about text are rarely that precise. You often want every customer whose name starts with a certain letter, every product code that contains a fragment, or every email hosted on a particular domain. The LIKE operator answers these questions by comparing a column against a pattern rather than a fixed string, and it returns the rows where the pattern is satisfied.

A pattern is just ordinary text with one or two special characters mixed in. Those special characters, called wildcards, stand in for unknown parts of the value. In standard SQL there are two: the percent sign, which represents any sequence of characters, and the underscore, which represents exactly one character.

Basic syntax

LIKE appears inside the WHERE clause and is followed by the pattern in single quotes. The examples below use a Customers table with columns such as customer_name, city, and country.

SELECT customer_name, city
FROM Customers
WHERE customer_name LIKE 'M%';

The query above returns every customer whose name starts with M, because the percent sign after M allows any characters to follow. To look for a fragment anywhere inside the value, place a percent sign on both sides of the text.

SELECT customer_name, country
FROM Customers
WHERE customer_name LIKE '%and%';

SELECT customer_name, city
FROM Customers
WHERE city LIKE 'L_nd_n';

Excluding matches with NOT LIKE

Adding NOT in front of LIKE inverts the test, returning the rows that do not fit the pattern. This is useful for filtering out a group instead of selecting it.

• Use LIKE when you know only part of a value or its general shape.
• Use the equals sign instead when you know the value exactly, since it is simpler and usually faster.
• Patterns that begin with a wildcard, such as '%son', often cannot use an index efficiently, so they scan more rows on large tables.
• NOT LIKE returns everything that fails the pattern, which is handy for exclusions.