MySQL LIKE

Last updated: October 19, 2026Oct 19, 2026 5 min read

The LIKE operator matches text against a pattern instead of an exact value. Use it for searches such as "names starting with Ra", "emails at corp.example" or "phone numbers beginning +91". Patterns are built from ordinary characters plus two wildcards.

1. The Two Wildcards

2. Starts With, Ends With, Contains

SELECT name FROM contacts WHERE name LIKE 'Ra%';

Result: 2 rows

SELECT name, email FROM contacts WHERE email LIKE '%@corp.example';

Result: 3 rows

SELECT name FROM contacts WHERE name LIKE '%esh%';

Result: 2 rows

SELECT name FROM contacts WHERE name LIKE '_a%';   -- second letter is 'a'

Result: 4 rows

3. NOT LIKE

SELECT name, phone FROM contacts WHERE phone NOT LIKE '+91%';

Result: 1 row

NULL never matches LIKE or NOT LIKE. Kiran's note is NULL, so he appears in neither of these:

SELECT COUNT(*) AS has_discount FROM contacts WHERE note LIKE '%discount%';

Result: 1 row

SELECT COUNT(*) AS no_discount FROM contacts WHERE note NOT LIKE '%discount%';

Result: 1 row

4. Searching for % or _ Themselves

To find a literal % or _, escape it with a backslash (or choose your own escape character with ESCAPE):

SELECT name, note FROM contacts WHERE note LIKE '%\\%%';   -- notes containing a % sign

Result: 2 rows

SELECT name, email FROM contacts WHERE email LIKE '%!_%' ESCAPE '!';   -- emails containing an underscore

Result: 2 rows

Without escaping, _ matches any character, so this matches every email:

SELECT COUNT(*) AS matches FROM contacts WHERE email LIKE '%_%';

Result: 1 row

5. Case Sensitivity

With the default utf8mb4_0900_ai_ci collation LIKE ignores case (and accents):

SELECT name FROM contacts WHERE name LIKE 'ra%';

Result: 2 rows

For a case-sensitive match, use a binary or case-sensitive collation:

SELECT name FROM contacts WHERE name LIKE 'ra%' COLLATE utf8mb4_0900_as_cs;

Result: Empty set (0 rows)

SELECT name FROM contacts WHERE name LIKE 'Ra%' COLLATE utf8mb4_0900_as_cs;

Result: 2 rows

6. LIKE on Numbers and Dates

LIKE converts its operand to text, so it works on numbers and dates, but a range condition is better: it is clearer and can use an index.

SELECT id, name FROM contacts WHERE id LIKE '%5%';

Result: 1 row

7. LIKE and Indexes

A pattern with a fixed start ('Ra%') can use an index like a range search. A leading wildcard ('%an') can't, so MySQL reads every row:

CREATE INDEX idx_name ON contacts (name);
EXPLAIN SELECT id FROM contacts WHERE name LIKE 'Ra%';
EXPLAIN SELECT id FROM contacts WHERE name LIKE '%an';

'Ra%': index range

'%an': index fully scanned

8. When LIKE Isn't Enough: REGEXP

REGEXP (or RLIKE) supports full regular expressions, for example "Indian mobile numbers starting with 98":

SELECT name, phone FROM contacts WHERE phone REGEXP '^\\+91 98';

Result: 4 rows

SELECT name, email FROM contacts WHERE email REGEXP '^[a-z]+\\.[a-z]+@';   -- first.last@ style

Result: 2 rows

9. LIKE from Java (Safely)

Bind the pattern as a parameter, and escape wildcards typed by the user so they are searched literally:

String userInput = "50%";
String escaped = userInput.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");
try (PreparedStatement ps = con.prepareStatement("SELECT name FROM contacts WHERE note LIKE ?")) {
    ps.setString(1, "%" + escaped + "%");            // contains the literal text 50%
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}

10. Practice Exercises

SELECT name, city FROM contacts WHERE city LIKE '%uru';

SELECT name FROM contacts WHERE name LIKE '____ %' AND name NOT LIKE '% % %';

11. Summary

• LIKE matches patterns: % = any characters, _ = one character.
• Starts with 'x%', ends with '%x', contains '%x%'.
• Escape literal % and _ with \ or a custom ESCAPE character.
• Case-insensitive by default; use a _cs or _bin collation for exact case.
• Leading wildcards can't use indexes; consider FULLTEXT or REGEXP for richer searches.