The = operator from SQL WHERE only finds exact matches. Real searches are looser: a shopper types “rice” and expects to see Ponni Rice; an admin wants every postal code that starts with 60. The SQL LIKE operator matches text against a pattern, which makes this kind of search possible.
The two wildcards
A LIKE pattern is ordinary text with two special characters:
| Wildcard | Matches | Example pattern | Would match |
|---|---|---|---|
% | Any number of characters – including none | 'M%' | Mumbai, Madurai, Mysuru |
_ | Exactly one character | '60____' | 600017, 605001 |
Everything else in the pattern must match as it is. Without any wildcard, LIKE 'Chennai' behaves almost like = 'Chennai' – the difference is case, explained below.
Starts with, ends with, contains
Where you put the % decides the kind of search:
'K%'– starts with K'%Mart'– ends with Mart'%tea%'– contains “tea” anywhere
SELECT CustomerName, City
FROM Customers
WHERE City LIKE 'M%';Returns 3 rows: Harbour Foods (Mumbai), Jasmine Mini Mart (Madurai) and Kaveri Wholesale (Mysuru).
SELECT ProductName, Unit, Price
FROM Products
WHERE ProductName LIKE '%flour%';Returns 1 row: Ragi Flour – even though the pattern is lower case.
Each LIKE takes a single pattern. To search for several words, join conditions with OR: WHERE ProductName LIKE '%rice%' OR ProductName LIKE '%atta%'. To require several words in any order, use AND instead. Keep the patterns simple – a search that is hard to read is also hard to debug.
Is SQL LIKE case-sensitive?
The last example found Ragi Flour with a lower-case pattern. In SQLite, LIKE ignores case – but only for the 26 English letters (ASCII). Letters such as é or Tamil and Hindi characters are compared exactly. This is different from =, which is case-sensitive in SQLite.
In MySQL, case sensitivity depends on the column’s collation. The default collation for new tables in MySQL 8 (utf8mb4_0900_ai_ci) is case-insensitive and also ignores accents, so LIKE and = both ignore case there. PostgreSQL’s LIKE is case-sensitive and offers ILIKE for case-insensitive matching.
Tip: If you need a case-sensitive pattern search in SQLite, use GLOB. It uses * and ? as wildcards instead of % and _: City GLOB 'M*'. GLOB is SQLite-only.
Exactly one character with _
The underscore is useful when you know the length of a value. Indian PIN codes have six digits, so '60____' (60 followed by four underscores) means “six characters starting with 60”:
SELECT CustomerName, City, PostalCode
FROM Customers
WHERE PostalCode LIKE '60____';Returns 2 rows: Annapoorna Stores (600017) and Bay Side Cafe (605001).
NOT LIKE
Put NOT in front to exclude matches. Combined with AND and OR, you can build quite specific filters. This query finds products that are not sold in packs:
SELECT ProductName, Unit
FROM Products
WHERE Unit NOT LIKE '%pack%'
ORDER BY ProductName;Returns 6 rows, including Cow Ghee (500 ml jar), Ponni Rice (5 kg bag) and Toned Milk (1 litre).
Remember that NULL never matches a pattern, and NOT LIKE does not match it either. A row with no unit would be left out of both results – add OR Unit IS NULL if you need it.
Searching for a real % or _
What if the text itself contains a percent sign, such as “100% Natural”? Choose an escape character with the ESCAPE clause, and put it in front of the wildcard you want treated as a normal character. Here \% means “a real percent sign”:
INSERT INTO Products (ProductName, CategoryID, Unit, Price)
VALUES ('Rice Flakes 100% Natural', 4, '500 g pack', 65);
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%100\%%' ESCAPE '\';Returns 1 row: Rice Flakes 100% Natural.
SQLite has no default escape character, so you must always write the ESCAPE clause. MySQL treats backslash as the escape character by default. SQL Server adds its own pattern feature, character ranges such as [A-C]%, which SQLite and MySQL do not support in LIKE.
SQL LIKE and performance
Watch out: A pattern that starts with %, like '%rice%', cannot use a normal index, so the database checks every row. That is fine for thousands of rows but slow for millions. Patterns that start with fixed text, like 'Rice%', can sometimes use an index. For a proper search box on a large site, look at full-text search: FULLTEXT indexes in MySQL or the FTS5 extension in SQLite.
When you build a search box in PHP, never glue the user’s text into the SQL string. Use a placeholder and add the wildcards to the value, such as '%' . $term . '%', as shown in PHP MySQL with PDO. Next, SQL IN and BETWEEN covers two more handy shortcuts for WHERE. To revisit combining tables, see SQL Joins.