SQL Topics
Wildcard Characters
title: Wildcard Characters
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.
What are Wildcard Characters?
Wildcard characters are special symbols that represent unknown or variable characters in a search pattern.
Instead of searching for an exact value:
SELECT *
FROM Students
WHERE Name = 'Rahul';You can search using patterns:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Result:
Rahul
Rohan
Raj
RakeshWildcards make searches more flexible.
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 CharactersExample:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Matches:
Rahul
Rohan
Raj
RakeshEvery name begins with R.
Names Ending with a Character
Example:
SELECT *
FROM Students
WHERE Name LIKE '%a';Matches:
Neha
PriyaThe name must end with:
aSearching for Text Anywhere
Example:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Matches:
iPhone
SmartPhone
Phone Case
HeadphoneThe text can appear anywhere.
Underscore (_) Wildcard
The underscore wildcard represents exactly one character.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R____';Matches:
Rahul
RakeshEach matching value contains:
R + 4 CharactersTotal length:
5 CharactersDifference Between % and _
Example:
LIKE 'A%'Matches:
A
Amit
Aman
AakashAny number of characters after A.
Example:
LIKE 'A____'Matches:
Amita
AmanaExactly 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
AakashRules:
Starts with A
Ends with hMultiple Underscores
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R___';Matches:
Rajs
RaniExactly:
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:
PhoneQuery:
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 charactersReality:
_ = 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 topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, wildcard
Related SQL Topics