SQL

LIKE in SQL

🔍 The LIKE operator in SQL is used to search for values that match a specific pattern in character (string) data. It is commonly used with the WHERE clause to filter rows based on partial text, prefixes, suffixes, or specific character patterns. The LIKEoperator is especially useful when the exact value is unknown.

📖 What is the LIKE Operator?

The LIKE operator compares a string value against a pattern. The pattern can contain normal characters as well as wildcard characters that represent one or more unknown characters.

Information

The LIKE operator is designed for pattern matching in text values. It is generally used with string data types such as CHAR, VARCHAR, and TEXT.

📝 Basic Syntax

General Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

🌟 Wildcard Characters

SQL uses wildcard characters with the LIKE operator to represent unknown characters.

Wildcard	Description	Example
%	Matches zero or more characters.	'A%'
_	Matches exactly one character.	'J_n'

📊 Sample Table

Assume the following Students table:

StudentID	Name	Department	City
101	Alice	Computer Science	Chennai
102	Bob	Mathematics	Coimbatore
103	Charlie	Physics	Madurai
104	David	Computer Science	Chennai
105	Eva	Mathematics	Salem

🔤 Using the % Wildcard

1️⃣ Starts With

Find students whose names start with the letter A.

Starts With

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

2️⃣ Ends With

Find students whose names end with the letter e.

Ends With

SELECT *
FROM Students
WHERE Name LIKE '%e';

3️⃣ Contains

Find students whose names contain the letters ar.

Contains Text

SELECT *
FROM Students
WHERE Name LIKE '%ar%';

4️⃣ Exact Number of Characters Before or After

The % wildcard can appear anywhere within the pattern.

Department Contains Science

SELECT *
FROM Students
WHERE Department LIKE '%Science%';

🔠 Using the _ Wildcard

The _ wildcard matches exactly one character.

Example 1

Single Character Wildcard

SELECT *
FROM Students
WHERE Name LIKE '_ob';

This query matches names such as Bob.

Example 2

Exactly Five Characters

SELECT *
FROM Students
WHERE Name LIKE '_____';

This query returns names that contain exactly five characters.

🚫 Using NOT LIKE

The NOT LIKE operator returns rows that do not match a specified pattern.

NOT LIKE Example

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

🔗 Combining LIKE with AND

LIKE with AND

SELECT *
FROM Students
WHERE Name LIKE 'A%'
AND City = 'Chennai';

🔀 Combining LIKE with OR

LIKE with OR

SELECT *
FROM Students
WHERE Name LIKE 'A%'
OR Name LIKE 'D%';

📈 LIKE with ORDER BY

Pattern matching results can be sorted using the ORDER BY clause.

Filter and Sort

SELECT Name,
       Department
FROM Students
WHERE Department LIKE '%Science%'
ORDER BY Name ASC;

📊 Common LIKE Patterns

Pattern	Description
'A%'	Starts with A.
'%e'	Ends with e.
'%ar%'	Contains "ar".
'J_n'	Three-letter word beginning with J and ending with n.
'_____'	Exactly five characters.

⚠️ Case Sensitivity

Whether LIKE comparisons are case-sensitive depends on the database system, database configuration, and collation settings. Some databases perform case-insensitive comparisons by default, while others do not.

Important

If case sensitivity matters, consult your database's documentation. Some systems provide functions such as LOWER() or UPPER(), or support case-sensitive variants of pattern matching.

💼 Real-World Example

A university administrator wants to find all students whose names begin with the letter D or whose department contains the word Science.

Real-World Query

SELECT StudentID,
       Name,
       Department
FROM Students
WHERE Name LIKE 'D%'
OR Department LIKE '%Science%'
ORDER BY Name;

This query searches for students using partial text instead of requiring exact matches.

⚠️ Best Practices

Best Practice

Use LIKE for pattern matching instead of exact comparisons. Use the % wildcard for partial matches and the _wildcard when exactly one unknown character is expected. Be aware that patterns beginning with % may reduce index usage in many database systems, potentially affecting query performance.

🚀 Key Points to Remember

📌 LIKE performs pattern matching on text values.
📌 % matches zero or more characters.
📌 _ matches exactly one character.
📌 NOT LIKE excludes matching patterns.
📌 LIKE can be combined with AND, OR, and ORDER BY.
📌 Case sensitivity depends on the database system and its collation settings.

>>"The LIKE operator helps you find information even when you don't know the exact text you're searching for."

Summary

✅ The LIKE operator is an essential SQL feature for searching text using patterns. By mastering wildcard characters such as % and _, you can perform flexible searches, simplify user-driven filtering, and create powerful text-based SQL queries.