SQL LIKE Operator: Wildcard Pattern Matching with % and _
A step-by-step, hands-on SQL lesson for searching text patterns — with a live browser SQL playground and 10-question quiz.
Exact matching is useful when you know the complete value, but real databases often require partial searches: names that start with Jo, emails ending in a certain domain, product names containing a word, or codes with one unknown character. In this lesson, you will learn the SQL LIKE operator and its two most important wildcards: % for any number of characters and _ for exactly one character.
Introduction
What You'll Learn — and Why It Matters
🎯 Objective
By the end of this lesson, you will be able to:
- Use
LIKEwithWHEREto perform pattern-based text searches. - Use
%to match zero, one, or many characters. - Use
_to match exactly one character. - Build prefix, suffix, contains, and fixed-length pattern searches.
- Understand the difference between
LIKE 'Sam%',LIKE '%Sam%', andLIKE '_am'. - Recognize case-sensitivity differences that can depend on the database, collation, and configuration.
⚠️ What to Avoid
- Using
=when you actually need a partial match, such as searching for names beginning withJo. - Forgetting the quotes around a text pattern: use
LIKE 'Jo%'. - Confusing
%with_:%can represent many characters, while_represents exactly one character. - Putting
%at both ends without thinking about performance on a large table. A leading wildcard can prevent efficient use of an ordinary index in many database systems. - Assuming that
LIKEis always case-sensitive or always case-insensitive. Behavior depends on the database and collation/settings.
💡 Important to Know
LIKE does not mean “similar” in a fuzzy-search or typo-correction sense. It performs pattern matching. The wildcard % means any sequence of characters, including an empty sequence, and _ means exactly one character.
| Pattern | Meaning | Example matches |
|---|---|---|
'Jo%' | Starts with Jo | John, Joanna, Jordan |
'%son' | Ends with son | Jackson, Wilson |
'%sql%' | Contains sql | SQL Course, MySQL, SQL Basics |
'_an' | Exactly 3 characters, ending in an | Dan, Ian, Jan |
'A__%' | Starts with A, then at least two characters | Alan, Apple, Alice |
Requirements
What You'll Need Before Starting
SELECT, FROM, WHERE, and basic comparison/filtering from earlier modules.No local database is required for the live playground further down the page. It uses SQLite compiled to WebAssembly and runs inside your browser.
If SQL is new to you, start with Module 1: Mastering the SQL SELECT Statement, then return here to practice pattern matching.
Practice
Step-by-Step Practice
Complete the steps in order. Run each query in MySQL/phpMyAdmin, then try the same query in the live playground.
1 Open MySQL or phpMyAdmin
Open a terminal and connect to MySQL, or start XAMPP and open phpMyAdmin in your browser. The exact startup process differs by operating system.
mysql -u root -p
General LIKE syntax:
SELECT column1, column2 FROM table_name WHERE text_column LIKE 'pattern';
2 Create a Database and Verify It
CREATE DATABASE module11_practice; SHOW DATABASES; USE module11_practice;
Confirm that module11_practice appears in the database list before continuing.
3 Create the Practice Table
CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(80), email VARCHAR(120), city VARCHAR(60), code VARCHAR(20) ); SHOW TABLES; DESCRIBE customers;
You should see the customers table and its five columns.
4 Insert Sample Data and Verify It
INSERT INTO customers (id, name, email, city, code) VALUES (1, 'John Smith', 'john@example.com', 'Phnom Penh', 'KH-101'), (2, 'Joanna Lee', 'joanna@example.com', 'Siem Reap', 'KH-102'), (3, 'Jordan Kim', 'jordan@demo.com', 'Phnom Penh', 'KH-203'), (4, 'Sam Wilson', 'sam@demo.com', 'Battambang', 'US-301'), (5, 'Jackson Brown', 'jackson@example.com', 'Kampot', 'US-412'), (6, 'Daniel Tan', 'daniel@test.com', 'Phnom Penh', 'SG-515'), (7, 'Dara Sok', 'dara@test.com', 'Kandal', 'KH-606'), (8, 'Mina Chan', 'mina@sample.com', 'Takeo', 'KH-707'); SELECT * FROM customers;
5 Use LIKE with % — Starts With
Find every customer whose name starts with Jo. The wildcard can match the rest of the name.
SELECT id, name FROM customers WHERE name LIKE 'Jo%';
This should find names such as John Smith, Joanna Lee, and Jordan Kim under normal case-insensitive comparison settings.
6 Use % — Contains and Ends With
To find text anywhere inside a value, put % on both sides. To find a suffix, put % before the suffix.
-- Contains 'demo' SELECT name, email FROM customers WHERE email LIKE '%demo%'; -- Ends with 'son' SELECT name FROM customers WHERE name LIKE '%son';
7 Use _ — Exactly One Unknown Character
The underscore matches exactly one character. For example, '_an' is a three-character pattern: one unknown character followed by an.
SELECT name FROM customers WHERE name LIKE 'Dan%'; SELECT code FROM customers WHERE code LIKE 'KH-10_';
The second query demonstrates that _ can be useful when one character is unknown but the rest of the structure is known.
8 Combine Patterns with Other Conditions
You can combine LIKE with other filters. Here we search for customers whose name begins with J and who live in Phnom Penh.
SELECT name, city FROM customers WHERE name LIKE 'J%' AND city = 'Phnom Penh';
9 Practice a Real-World Search
Search for customer email addresses that end with @example.com, then sort the results alphabetically.
SELECT name, email FROM customers WHERE email LIKE '%@example.com' ORDER BY name ASC;
Modify the query to find codes beginning with KH-. Then change it so it finds codes with exactly three digits after KH-.
Troubleshooting
Common Problems and Solutions
Problem 1: I used = and got no rows
You tried WHERE name = 'Jo%' expecting a wildcard search.
LIKE for pattern matching: WHERE name LIKE 'Jo%'. The % wildcard has special meaning with LIKE.Problem 2: I confused % and _
Your pattern returns too many or too few rows.
% = zero or more characters; _ = exactly one character. Choose the wildcard according to how much of the value is unknown.Problem 3: The search is case-sensitive on my system
A query such as LIKE 'jo%' does not match values beginning with Jo.
Problem 4: I want to search for a literal % or _
You need to find a value that actually contains a percent or underscore character.
LIKE '%\\%%' for a literal percent and LIKE '%\\_%' for a literal underscore, using the backslash escape where configured. An explicit ESCAPE clause is supported by some SQL dialects, so check your database documentation.Problem 5: My LIKE '%word%' query is slow
The query works but becomes slow when the table contains many rows.
% often makes it difficult for a normal B-tree index to narrow the search efficiently. Prefer a prefix search such as 'word%' when it matches your requirement, and investigate full-text/search-specific solutions for large-scale substring searching.Conclusion
What You Should Take Away
You should now understand how LIKE turns a normal SQL filter into a pattern search. The most important rule is simple: % matches any sequence of characters, while _ matches exactly one character.
After this lesson, you should be able to build searches for names, emails, product titles, city names, codes, and other text values without needing to know the complete value first.
The next lessons can build on this foundation by combining filters, handling NULL, sorting results, and eventually using more advanced SQL search and aggregation techniques.
Homework
Homework & Quiz
📝 Homework Task
Using the customers table from this lesson, write queries that:
- Find customers whose name starts with
Da. - Find emails ending with
@demo.com. - Find names containing
an. - Find codes beginning with
KH-. - Find codes matching
KH-10_, then explain why the underscore matches one character.
Try the queries yourself before checking the live playground.
🧪 Quiz: Test Your Understanding (10 Questions)
Choose one answer for each question, then click Submit Quiz. The blue tick marks are checkbox-style, but JavaScript enforces one answer per question.
1. Which operator is used for SQL pattern matching?
2. What does % represent in a LIKE pattern?
3. What does _ represent?
4. Which pattern finds names that start with Jo?
5. Which pattern searches for a value containing sql anywhere?
6. What does LIKE 'A__%' require?
7. Which clause is correct for a partial text search?
8. What is a common performance concern with LIKE '%word%' on a large table?
9. Does LIKE have identical case-sensitivity behavior in every SQL database?
10. Which query finds email addresses ending with @example.com?
Try It Live
🧪 Live SQL Playground — LIKE
This is a real SQLite database running entirely in your browser through WebAssembly. The sample data is created locally in the page. Use the examples, edit the SQL, and run your own LIKE patterns.
name TEXT
email TEXT
city TEXT
code TEXT
Related Lessons
Continue Learning
Build the SELECT and WHERE foundation used in this lesson.
Set up a local Apache/MySQL/phpMyAdmin environment.
Install and prepare MySQL before running the practice queries.
FAQ
Frequently Asked Questions
1. What is the SQL LIKE operator?
LIKE is used with a pattern to filter text values. It is commonly used with % and _ wildcards.
2. What does the % wildcard mean?
% matches zero or more characters. For example, LIKE 'Jo%' matches values beginning with Jo.
3. What does the _ wildcard mean?
_ matches exactly one character. For example, LIKE 'KH-10_' matches a seven-character pattern where the final character is unknown.
4. What is the difference between % and _?
% can represent any sequence of characters, including no characters. _ represents exactly one character.
5. Can LIKE search anywhere in a string?
Yes. Put % on both sides, such as LIKE '%sql%', to search for a sequence anywhere in the value.
6. Is LIKE case-sensitive?
It depends on the SQL database and its collation/settings. In MySQL, collation is an important part of determining comparison behavior.
7. Can I use LIKE with numbers?
LIKE is intended for pattern matching of character/string values. If you need numeric ranges, use comparison operators such as >, <, >=, and <=.
8. How do I search for a literal % or _?
Use an escape character according to your database's rules. In MySQL, backslash is commonly used for escaping LIKE wildcard characters, subject to SQL mode and configuration.
9. Why can LIKE '%word%' be slow?
A leading wildcard can make a normal index less useful for locating the beginning of the search term. For large datasets, consider prefix searches or database-specific full-text/search solutions when appropriate.
10. Is the live playground the same as MySQL?
No. The playground uses SQLite compiled to WebAssembly for browser-based practice. The SQL LIKE examples are designed to be compatible in principle, but MySQL-specific behavior such as collation and escaping can differ.
){kind=link}
0 Comments