SQL LIKE and Wildcards

The SQL LIKE operator searches for a pattern in a text column. It is how you build simple search features such as "find customers whose name starts with G".

Patterns use two wildcards: % matches any number of characters (including none) and _ matches exactly one character.

Starts With

Find customers whose name starts with the letter G.

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'G%';

CustomerName
-------------
Golden Grains
(1 row)

Ends With and Contains

A % at the start means "ends with"; % on both sides means "contains". This finds products whose name contains "ea" anywhere.

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%ea%';

ProductName
------------
Green Tea
Coffee Beans
Wheat Flour
(3 rows)

The Underscore Wildcard

Each _ matches exactly one character. This finds cities with exactly six letters that start with "L".

SELECT DISTINCT City
FROM Customers
WHERE City LIKE 'L_____';

City
------
Lahore
London
(2 rows)

NOT LIKE

NOT LIKE excludes matching rows.

SELECT ProductName
FROM Products
WHERE ProductName NOT LIKE '%e%';

ProductName
-----------
Almonds
(1 row)

Case Sensitivity

• SQLite: LIKE is case-insensitive for English letters
• MySQL: depends on the column collation, usually case-insensitive
• PostgreSQL: LIKE is case-sensitive; use ILIKE for case-insensitive
• SQL Server: depends on collation, usually case-insensitive and also supports [abc] character sets

Frequently asked questions

How do I search for a literal % or _ with LIKE?

Use an escape character: WHERE code LIKE '50!%' ESCAPE '!' matches the text 50%.

Why is LIKE '%word%' slow?

A leading % prevents normal indexes from being used, so the database scans every row. For large text search use a full-text index instead.

What is the difference between LIKE and = in SQL?

= compares the whole value exactly, while LIKE compares against a pattern with wildcards. Without wildcards, LIKE behaves almost like =, apart from case rules on some databases.