LIKE Pattern Matching

Overview

The LIKE operator enables wildcard pattern matching to perform simple text searches on string columns.

Learning Objectives

• Master pattern matching syntax using % and _ wildcards.
• Compare case-sensitive vs. case-insensitive operators (LIKE vs. ILIKE).
• Optimize text queries by understanding how prefix wildcards impact index utilization.

Detailed Concept Explanation

SQL provides two basic wildcard characters for pattern matching:

• %: Matches zero or more characters.
• _: Matches exactly one character.

Case Sensitivity

In the SQL standard, LIKE is case-sensitive. Some databases (like PostgreSQL) support ILIKE for case-insensitive searches, while others (like MySQL/SQL Server) have collation rules that make LIKE case-insensitive by default.

Code Examples

SQL

-- Match emails ending with '@gmail.com'
SELECT username, email 
FROM users 
WHERE email LIKE '%@gmail.com';

-- Match names where the second character is 'a'
SELECT name 
FROM customers 
WHERE name LIKE '_a%';

-- Exclude records starting with 'Test'
SELECT project_name 
FROM projects 
WHERE project_name NOT LIKE 'Test%';

Best Practices

• Avoid Leading Wildcards: Avoid placing wildcards at the beginning of a pattern (like %search) on large tables. This prevents the database from using indexes, forcing a slow table scan.
• Escape Characters: If you need to search for actual % or _ characters, use the ESCAPE clause (e.g., WHERE code LIKE '%\_%' ESCAPE '').

Interview Perspective

Interactive Challenges

Summary

LIKE supports pattern matching with % (any characters) and _ (one character). Keep searches prefixed to protect query speed.