SQL LIKE Operator

LIKE matches text against a pattern using wildcards — for partial or fuzzy string matching.

Syntax & Wildcards

Sample Table: employees

Examples

-- Names starting with "A"
SELECT name FROM employees WHERE name LIKE 'A%';

-- Names containing "an" anywhere
SELECT name FROM employees WHERE name LIKE '%an%';

-- Company emails only
SELECT name FROM employees WHERE email LIKE '%@codingnowai.in';

-- Exactly 4 characters, starting with "K"
SELECT name FROM employees WHERE name LIKE 'K___';

Output for 'A%': Aman Sharma.
Output for '%an%': Aman Sharma, Karan Mehta (both contain "an").

Case Sensitivity

MySQL's default collation is usually case-insensitive, so LIKE 'a%' and LIKE 'A%' often return the same rows. PostgreSQL's LIKE is case-sensitive by default — use ILIKE there for case-insensitive matching.

Practical Use Case

Search boxes ("find any product with 'phone' in the name"), filtering by email domain, or matching loosely-formatted codes.

Common Mistakes

• Leading wildcard performance. LIKE '%something' (wildcard at the start) usually can't use a standard index efficiently, since the database can't jump to a starting point — it has to scan every row. LIKE 'something%' (wildcard only at the end) can use an index.
• Forgetting to escape literal % or _ characters when they're part of the actual data you're searching for (use ESCAPE or a database-specific escape function)
• Using LIKE '%value%' for a full-text search need — at scale, a dedicated full-text index is a better tool

Interview Relevance

Q: "Why is LIKE '%term' slower than LIKE 'term%'?" — tests whether you understand how B-tree indexes work, not just LIKE syntax.

Practice Question

Write a query to find all customers whose email ends in .edu.

Related SQL Notes

• WHERE Clause
• String Functions
• Indexes