LIKE Operator in SQL

The LIKE operator is used to search for a specific pattern inside text values.

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

This finds students whose names start with the letter A.

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

The pattern specifies what kind of text you want to find.

The % wildcard represents zero, one, or multiple characters.

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

This finds names that start with A, such as Amit, Ankit, and Anjali.

When % is placed at the beginning, SQL searches for values ending with the specified text.

SELECT *
FROM Students
WHERE name LIKE '%n';

This finds names that end with the letter n.

When % is placed at both ends, SQL searches for the specified text anywhere in the value.

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

This finds names containing the text "an".

The underscore _ wildcard represents exactly one character.

SELECT *
FROM Students
WHERE name LIKE 'A_i';

This pattern matches three-character names that start with A and end with i.

Multiple underscore characters can be used when you want to match a specific number of characters.

SELECT *
FROM Students
WHERE name LIKE 'A____';

Here, A is followed by exactly four characters.

SELECT *
FROM Students
WHERE city LIKE 'Pat%';

This finds cities beginning with "Pat".

SELECT *
FROM Students
WHERE course LIKE '%Python%';

This searches for course values containing the word Python.

LIKE can also be useful when searching text representations of phone numbers.

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

This finds mobile values beginning with 98.

The NOT LIKE operator is used when you want to exclude a particular pattern.

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

This returns names that do not start with A.

LIKE is normally used inside a WHERE condition.

SELECT name, city
FROM Students
WHERE city LIKE 'P%';

This returns students whose city starts with P.

SELECT *
FROM Students
WHERE name LIKE 'A%'
AND city = 'Patna';

This finds students whose names start with A and whose city is Patna.

SELECT *
FROM Students
WHERE city LIKE 'P%'
OR city LIKE 'D%';

This finds cities beginning with P or D.

SELECT name, city
FROM Students
WHERE name LIKE 'A%'
ORDER BY name ASC;

The matching names are displayed in ascending alphabetical order.

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

In MySQL, this returns up to five matching records.

SELECT name
FROM Students
WHERE name LIKE 'A%';

The % wildcard allows any number of characters after A.

SELECT name
FROM Students
WHERE name LIKE '%a';

This finds names ending with the letter a.

SELECT *
FROM Students
WHERE name LIKE '%raj%';

This searches for "raj" anywhere inside the name.

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

This finds students whose names start with A and whose course contains the word Python.

LIKE can be combined with other conditions such as IN.

SELECT *
FROM Students
WHERE name LIKE 'A%'
AND city IN ('Patna', 'Delhi');

This finds students whose names start with A and who live in Patna or Delhi.

SELECT *
FROM Students
WHERE name LIKE 'A%'
AND age BETWEEN 18 AND 30;

This finds students whose names start with A and whose age is between 18 and 30.

SELECT DISTINCT city
FROM Students
WHERE city LIKE 'P%';

This displays unique cities beginning with P.

SELECT COUNT(*)
FROM Students
WHERE name LIKE 'A%';

This counts the number of students whose names start with A.

LIKE can also be used with UPDATE to find records matching a pattern.

UPDATE Students
SET status = 'Active'
WHERE name LIKE 'A%';

This updates matching records whose names start with A.

LIKE can also be used with DELETE to remove records matching a pattern.

DELETE FROM Students
WHERE name LIKE 'Test%';

This deletes records whose names start with "Test".

A common mistake is forgetting the wildcard when you want a partial match.

Exact match:

SELECT *
FROM Students
WHERE name = 'Amit';

Pattern match:

SELECT *
FROM Students
WHERE name LIKE 'Ami%';

The second query can match values beginning with "Ami".

Whether LIKE comparisons are case-sensitive depends on the database system and, in systems such as MySQL, the collation of the column.

SELECT *
FROM Students
WHERE name LIKE 'amit%';

Always understand the text comparison and collation rules of the database you are using.

Suppose an institute wants to find students whose names start with A, who are studying Python, and who live in Patna.

SELECT name, city, course
FROM Students
WHERE name LIKE 'A%'
AND course LIKE '%Python%'
AND city = 'Patna';

This query combines LIKE with other conditions to create a useful search.

SELECT name, age, course, city, fee
FROM Students
WHERE name LIKE 'A%'
AND course IN ('Python', 'Java')
AND city LIKE 'P%'
AND age BETWEEN 18 AND 30
ORDER BY name ASC;

This query:

• Finds students whose names start with A.
• Allows Python or Java courses.
• Finds cities beginning with P.
• Checks students between 18 and 30 years old.
• Sorts the results alphabetically by name.