beginner

LIKE Pattern Matching

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

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

Challenge 1: Find Domain Registrations

Write a query to select all names from 'users' where the 'email' starts with 'support%'.


Summary

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

DPA

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.