SQL LIKE Operator & Wildcards: Pattern Matching Guide

When searching for names, product titles, or addresses, users rarely search for exact complete strings. Instead, they search for prefixes, substrings, or patterns. The LIKE operator combined with SQL Wildcards provides powerful text pattern matching capabilities directly within the database engine.

The Two Standard SQL Wildcards

Hands-on Pattern Matching Queries

-- 1. Find all students whose first name starts with 'A'
SELECT student_id, first_name, email
FROM students
WHERE first_name LIKE 'A%';

-- 2. Find all students whose email is on gmail.com
SELECT student_id, first_name, email
FROM students
WHERE email LIKE '%@gmail.com';

-- 3. Find students whose first name has 'a' as the second character
SELECT student_id, first_name
FROM students
WHERE first_name LIKE '_a%';

-- 4. Find students whose name contains 'an' anywhere
SELECT student_id, first_name
FROM students
WHERE first_name LIKE '%an%';

Escaping Literal Wildcards with the ESCAPE Clause

If your database stores literal percentage signs (e.g., '10% discount') or underscores (e.g., 'order_101'), define an explicit escape character:

-- Search for literal discount string containing '%'
SELECT product_name, discount_label
FROM products
WHERE discount_label LIKE '%10!%%' ESCAPE '!';

Next, let us learn how to filter sets and numeric ranges using SQL IN & BETWEEN Operators!

Frequently Asked Questions (FAQ)