MySQL LIKE

The LIKE operator searches for a pattern in a column.

SELECT *
FROM students
WHERE name LIKE 'A%';

This finds students whose names start with A.

The basic syntax is:

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

The pattern tells MySQL what kind of text to search for.

The % wildcard represents zero or more characters.

SELECT *
FROM students
WHERE name LIKE 'A%';

This can match names such as Amit, Ankit, and Arun.

To find names beginning with a particular letter, place % after the letter.

SELECT *
FROM students
WHERE name LIKE 'R%';

This finds names beginning with R.

To find names ending with a particular letter, place % before the letter.

SELECT *
FROM students
WHERE name LIKE '%a';

This finds names ending with the letter a.

To find text containing a particular word or character sequence, use % on both sides.

SELECT *
FROM students
WHERE name LIKE '%an%';

This finds names containing an.

The underscore _ represents exactly one character.

SELECT *
FROM students
WHERE name LIKE 'A_it';

This pattern can match a four-character name such as Amit.

Each underscore represents one character.

SELECT *
FROM students
WHERE name LIKE '_mit';

The underscore represents the first character.

You can use multiple underscores to represent multiple individual characters.

SELECT *
FROM students
WHERE name LIKE '____';

This searches for values containing exactly four characters.

LIKE is normally used together with WHERE.

SELECT name, course
FROM students
WHERE course LIKE 'P%';

This finds courses beginning with P.

LIKE can be combined with AND.

SELECT *
FROM students
WHERE name LIKE 'A%'
AND course = 'Python';

This finds Python students whose names start with A.

Multiple LIKE conditions can be combined with OR.

SELECT *
FROM students
WHERE name LIKE 'A%'
OR name LIKE 'R%';

This finds names beginning with A or R.

NOT LIKE finds values that do not match a specified pattern.

SELECT *
FROM students
WHERE name NOT LIKE 'A%';

This excludes names beginning with A.

LIKE is useful for searching course names.

SELECT *
FROM courses
WHERE course_name LIKE 'Web%';

This can find courses whose names begin with Web.

LIKE can be used to search email addresses.

SELECT *
FROM students
WHERE email LIKE '%gmail.com';

This searches for email addresses ending with gmail.com.

LIKE can be used with phone-number strings when pattern searching is required.

SELECT *
FROM students
WHERE mobile LIKE '98%';

This searches for mobile values beginning with 98.

LIKE can be combined with ORDER BY.

SELECT name, course
FROM students
WHERE name LIKE 'A%'
ORDER BY name ASC;

The matching names are sorted alphabetically.

LIMIT can be used to restrict the number of matching records.

SELECT *
FROM students
WHERE name LIKE 'A%'
LIMIT 5;

This returns up to five matching records.

LIKE and IN solve different problems, but they can be used together.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
AND name LIKE 'A%';

This finds Python or Java students whose names begin with A.

LIKE can be combined with BETWEEN when multiple filtering conditions are needed.

SELECT *
FROM students
WHERE name LIKE 'A%'
AND age BETWEEN 18 AND 25;

This finds students whose names begin with A and whose age is between 18 and 25.

LIKE can be used to update records matching a pattern.

UPDATE students
SET course = 'Python'
WHERE name LIKE 'A%';

LIKE can also be used with DELETE.

DELETE FROM students
WHERE name LIKE 'Test%';

This deletes records whose names start with Test.

LIKE should not be used to check for NULL values. Use IS NULL instead.

SELECT *
FROM students
WHERE email IS NULL;

Use LIKE for pattern matching and IS NULL for NULL checking.

LIKE can be used together with a subquery when the filtering logic requires another query.

SELECT *
FROM students
WHERE name LIKE CONCAT(
    (SELECT 'A'),
    '%'
);

This creates a pattern beginning with A.

• Forgetting quotation marks around the pattern
• Using = when a pattern search is required
• Confusing % with _
• Using LIKE to check NULL values
• Forgetting the wildcard when partial matching is needed
• Using UPDATE or DELETE without first checking the matching records

A simple LIKE workflow is:

1. Choose the column to search.
2. Decide what pattern you need.
3. Choose % or _ as required.
4. Write the LIKE condition.
5. Test it using SELECT.
6. Add other conditions if necessary.

SELECT id, name, course
FROM students
WHERE name LIKE 'A%'
ORDER BY name;

Consider the following examples:

-- Starts with A
name LIKE 'A%'

-- Ends with a
name LIKE '%a'

-- Contains an
name LIKE '%an%'

-- Exactly four characters
name LIKE '____'

-- Four-character value starting with A
name LIKE 'A___'

Suppose we want to find students whose names start with A and who are enrolled in Python or Java.

SELECT id, name, course, age, fee
FROM students
WHERE name LIKE 'A%'
AND course IN ('Python', 'Java')
ORDER BY name ASC;

This combines LIKE, IN, AND, and ORDER BY in one query.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    email VARCHAR(150),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, email, fee)
VALUES
('Amit Kumar', 'Python', 22, 'amit@gmail.com', 15000.00),
('Anita Singh', 'Java', 21, 'anita@gmail.com', 18000.00),
('Rahul Kumar', 'PHP', 24, 'rahul@yahoo.com', 12000.00),
('Ravi Sharma', 'JavaScript', 26, 'ravi@gmail.com', 22000.00),
('Priya Das', 'Python', 20, 'priya@gmail.com', 15000.00);

SELECT id, name, course, email, fee
FROM students
WHERE name LIKE 'A%'
AND course IN ('Python', 'Java')
ORDER BY name ASC;

This query finds students whose names start with A and whose course is Python or Java.