MySQL LIKE and Wildcards

Written by RedoHub Editorial Team

The LIKE operator is used in a WHERE clause to search for a pattern within a text column, rather than matching an exact value. It is powered by two special wildcard characters that represent unknown parts of the pattern you are searching for.

The Two Wildcards

Percent Sign

Represents zero or more characters of any kind. Use it to match the beginning, end, or any part of a string.

Underscore

Represents exactly one character. Use it when you know the length but not the specific character in that position.

Syntax

SELECT columns FROM table_name
WHERE column_name LIKE 'pattern';

% Wildcard Examples

-- Names that START with 'R'
SELECT name FROM students WHERE name LIKE 'R%';
-- Returns: Rahim Uddin, Rafi Hossain

-- Names that END with 'Ali'
SELECT name FROM students WHERE name LIKE '%Ali';
-- Returns: Karim Ali

-- Names that CONTAIN 'im' anywhere
SELECT name FROM students WHERE name LIKE '%im%';
-- Returns: Rahim Uddin, Karim Ali

-- Cities that start with any characters but end with 'pur'
SELECT name, city FROM students WHERE city LIKE '%pur';
-- Returns cities ending in 'pur'

_ Wildcard Examples

-- Names where the second character is 'a'
SELECT name FROM students WHERE name LIKE '_a%';
-- Returns: Karim Ali, Nila Akter, Rafi Hossain, Sara Begum

-- Exactly 5-character city names
SELECT city FROM students WHERE city LIKE '_____';
-- Returns cities with exactly 5 characters (5 underscores)

-- Names with exactly 8 characters
SELECT name FROM students WHERE name LIKE '________';

Combining % and _

Both wildcards can be combined in a single pattern:

-- Names where the 3rd character is 'r'
SELECT name FROM students WHERE name LIKE '__r%';
-- Returns: Sara Begum (S-a-r...)

-- Email addresses from a specific domain with exactly 5 chars before @
SELECT email FROM users WHERE email LIKE '_____@gmail.com';

NOT LIKE

Use NOT LIKE to exclude rows that match a pattern:

-- Students whose name does NOT start with 'R'
SELECT name FROM students WHERE name NOT LIKE 'R%';
-- Returns: Sara Begum, Karim Ali, Nila Akter

Case Sensitivity

By default, LIKE in MySQL is case-insensitive for standard character sets. This means LIKE 'dhaka' matches 'Dhaka', 'DHAKA', and 'dhaka' equally.

To make a LIKE comparison case-sensitive, use the BINARY keyword:

-- Case-sensitive: only matches lowercase 'dhaka'
SELECT * FROM students WHERE city LIKE BINARY 'dhaka';

Performance Tip: LIKE 'value%' (pattern at the end) can use an index and is fast. LIKE '%value' or LIKE '%value%' (wildcard at the start) cannot use an index and performs a full table scan — avoid these on large tables where possible.

Key Points to Remember

LIKE matches a text pattern — use it when you do not know the exact value.
% matches zero or more characters; _ matches exactly one character.
LIKE 'R%' matches anything starting with R; LIKE '%R' matches anything ending with R.
NOT LIKE excludes rows that match the pattern.
LIKE is case-insensitive by default; use LIKE BINARY for case-sensitive matching.
Avoid leading wildcards (LIKE '%value') on large tables — they prevent index usage and slow queries.

What's next? The next lesson covers the IN operator — a clean way to check whether a column value matches any value in a list, without writing multiple OR conditions.

« Previous Lesson Next Lesson »