Pattern Matching with LIKE and Wildcards

When you're not sure about the exact value you're looking for in a column, pattern matching can help you find it using the LIKE operator. This is especially useful for partial matches or searching flexible text values .

🔍 What is LIKE ?

The LIKE operator is used in the WHERE clause to search for a specific pattern in a column.

🔤 Wildcards in SQL

Wildcard	Meaning	Example
%	Zero, one, or many characters	'A%' → A, Anuj
_	Exactly one character	'A_' → Aj, An

🧪 Our Sample Table: students

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	91.75
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	Advanced SQL	76.00

✅ Examples of LIKE Usage

📘 1. Names starting with ‘A’

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

Matches: Anjali Kapoor, Aman Verma

📘 2. Names ending with ‘a’

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

Matches: Riya Jain

📘 3. Names that contain ‘al’

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

Matches: Anjali Kapoor

📘 4. Names with 5 characters starting with ‘R’

SELECT * FROM students WHERE name LIKE 'R____' ;  

Matches: Riya (if full name was just ‘Riya’)

📘 5. Course names that start with ‘Adv’

SELECT * FROM students WHERE course LIKE 'Adv%' ;  

Matches: Advanced SQL

💡 Combining with NOT

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

Finds all names not starting with ‘A’

🔒 Case Sensitivity

• MySQL: Not case sensitive (default)
• PostgreSQL: Case sensitive
• Use ILIKE in PostgreSQL for case-insensitive match

🎯 Real-Life Scenarios

Scenario	Query Example
Emails ending with @gmail.com	WHERE email LIKE '%@gmail.com'
Names with 'son' in middle	WHERE name LIKE '%son%'
Phone numbers starting with 987	WHERE phone LIKE '987%'

🧠 Best Practices

• Use % wisely — %pattern% is powerful but slower.
• Avoid excessive wildcards on large datasets (can affect performance).
• For advanced search, consider full-text search (in future chapters).

📝 Summary

Wildcard	Purpose	Example
%	Any number of characters	'A%'
_	A single character	'A_'
NOT LIKE	Opposite of LIKE	'NOT LIKE A%'