SQL Notes
Learn about SQL Wildcard Characters, how they work with the LIKE operator, and how to perform advanced pattern matching for efficient data searching.
In modern database systems, users rarely search for exact values all the time. Most searches involve partial information.
For example:
- A customer remembers only the first few letters of a product name.
- An administrator wants all email addresses from a specific domain.
- A company needs employees whose names start with a certain character.
- A school wants students whose names contain a particular word.
Searching for such data using exact matches would be difficult and inefficient.
To solve this problem, SQL provides Wildcard Characters.
Wildcard characters are special symbols used with the LIKE operator to perform pattern-based searches. They help SQL identify values that partially match a given pattern rather than requiring exact matches.
Wildcard characters are widely used in search systems, filtering operations, reporting tools, and database applications.
Why are Wildcard Characters Important?
Consider a Customers table:
| CustomerID | Name |
|---|---|
| 1 | Rahul |
| 2 | Rohan |
| 3 | Raj |
| 4 | Amit |
Suppose you want all names beginning with:
RWithout wildcards:
WHERE Name = 'Rahul'
OR Name = 'Rohan'
OR Name = 'Raj'This becomes difficult as data grows.
With wildcards:
WHERE Name LIKE 'R%'The query is shorter, cleaner, and easier to maintain.
Wildcards and LIKE
Wildcard characters are typically used together with the LIKE operator.
Basic syntax:
SELECT *
FROM TableName
WHERE ColumnName LIKE Pattern;Example:
SELECT *
FROM Students
WHERE Name LIKE 'A%';Only matching records are returned.
Common SQL Wildcards
The most commonly used wildcard characters are:
| Wildcard | Description |
|---|---|
| % | Represents zero or more characters |
| _ | Represents exactly one character |
These two wildcards are supported by nearly all major database systems.
Percentage (%) Wildcard
The % wildcard matches any number of characters.
Including:
0 Characters 1 Character Many Characters
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Matches:
Rahul Rohan Raj Rakesh
Every name begins with R.
Names Ending with a Character
Example:
SELECT *
FROM Students
WHERE Name LIKE '%a';Matches:
Neha Priya
The name must end with:
aSearching for Text Anywhere
Example:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Matches:
iPhone SmartPhone Phone Case Headphone
The text can appear anywhere.
Underscore (_) Wildcard
The underscore wildcard represents exactly one character.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R____';Matches:
Rahul Rakesh
Each matching value contains:
R + 4 CharactersTotal length:
5 CharactersDifference Between % and _
Example:
LIKE 'A%'Matches:
A Amit Aman Aakash
Any number of characters after A.
Example:
LIKE 'A____'Matches:
Amita Amana
Exactly four characters after A.
Comparison:
| Wildcard | Meaning |
|---|---|
| % | Zero or more characters |
| _ | Exactly one character |
Combining Wildcards
Wildcards can be used together.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'A%h';Matches:
Ashish Aakash
Rules:
Starts with A
Ends with hMultiple Underscores
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R___';Matches:
Rajs Rani
Exactly:
4 Charactersmust exist.
Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100)
);Insert records:
INSERT INTO Students VALUES
(1, 'Rahul'),
(2, 'Rohan'),
(3, 'Amit'),
(4, 'Priya'),
(5, 'Neha');Finding Names Starting with R
Query:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 2 | Rohan |
Finding Names Ending with a
Query:
SELECT *
FROM Students
WHERE Name LIKE '%a';Result:
| StudentID | Name |
|---|---|
| 4 | Priya |
| 5 | Neha |
Finding Names Containing "ha"
Query:
SELECT *
FROM Students
WHERE Name LIKE '%ha%';Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 5 | Neha |
The text appears anywhere in the string.
Searching Email Domains
Example:
SELECT *
FROM Customers
WHERE Email LIKE '%@gmail.com';Result:
All Gmail users are returned.
Useful for customer segmentation and analytics.
Searching Product Names
Example:
SELECT *
FROM Products
WHERE ProductName LIKE '%Laptop%';Result:
Products containing the word Laptop are displayed.
Common in e-commerce search systems.
Real-World Example
Imagine an online shopping website.
Products:
| ProductName |
|---|
| ------------- |
| Smartphone |
| Phone Case |
| Laptop |
| Headphones |
Customer searches:
Phone
Query:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Result:
| ProductName |
|---|
| ------------- |
| Smartphone |
| Phone Case |
| Headphones |
Only relevant products appear.
Wildcards in Different Databases
Most modern databases support:
% _
Including:
- MySQL
- PostgreSQL
- SQL Server
- SQLite
- Oracle
This makes wildcard searches highly portable.
Wildcard Performance Considerations
Wildcard searches can impact performance.
Efficient:
LIKE 'A%'Often uses indexes.
Less Efficient:
LIKE '%A'May require scanning many records.
Slowest:
LIKE '%A%'Usually requires full table scanning.
Common Errors
Forgetting Quotes
Wrong:
WHERE Name LIKE R%Correct:
WHERE Name LIKE 'R%'Confusing % and _
Wrong assumption:
_ matches many characters
Reality:
_ = One Character
% = Many CharactersUsing LIKE Without Wildcards
Example:
WHERE Name LIKE 'Rahul'This behaves similarly to:
WHERE Name = 'Rahul'Expecting Case-Insensitive Matching
Behavior depends on the database system and collation settings.
Best Practices
Use Wildcards Only When Needed
Pattern matching can increase query cost.
Prefer Prefix Searches
Good:
LIKE 'A%'Often performs better.
Avoid Leading Wildcards on Large Tables
Example:
LIKE '%phone'May reduce performance.
Test Search Patterns
Always verify returned results.
Combine with Additional Filters
Example:
WHERE Name LIKE 'R%'
AND City = 'Delhi'Improves accuracy.
Common Interview Questions
What are SQL Wildcards?
Special characters used with LIKE for pattern matching.
What does % represent?
Zero or more characters.
What does _ represent?
Exactly one character.
Which operator commonly uses wildcards?
LIKEWhich wildcard is generally more flexible?
%because it can match any number of characters.
Summary
Wildcard characters are essential tools for pattern matching in SQL. They allow flexible searching, improve user experience, and support powerful text-based filtering operations.
In this lesson, you learned:
- What wildcard characters are
- Why they are important
- The
%wildcard - The
_wildcard - Combining wildcards
- Email and product searches
- Performance considerations
- Common mistakes
- Best practices
Understanding wildcard characters is important because pattern-based searching is a core feature of modern database applications.
Next Step
Continue to the next lesson:
Aggregate Functions →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Wildcard Characters.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, filtering, wildcard, characters
Related SQL Complete Guide Topics