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 (
LIKEvs.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 theESCAPEclause (e.g.,WHERE code LIKE '%\_%' ESCAPE '').
Interview Perspective
Danger
Performance Note: Prefix Sargs
An interviewer may ask: "Which query is faster: LIKE 'Admin%' or LIKE '%Admin%'?"
Answer: LIKE 'Admin%' is much faster. Since the start of the string is fixed (a prefix query), the database engine can perform an index range seek. A leading wildcard (%Admin%) forces a full table scan, loading and evaluating every row.
Interactive Challenges
Summary
LIKE supports pattern matching with % (any characters) and _ (one character). Keep searches prefixed to protect query speed.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.