MySQL LIKE and Wildcards

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

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';

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.