In this lesson, you will learn how to locate specific information in your database even when you don't know the exact value of a field.
Searching Beyond Exact Matches
Up until now, you have used operators like = or <> to find data. These work perfectly when you know the exact name or ID of a record. However, real-world data is often messy or incomplete. Sometimes, you only know part of a string, such as a last name that starts with "Sm" or an email address that contains "gmail".
To handle this, SQL provides the LIKE operator, which allows us to perform "fuzzy" searches using special characters known as wildcards.
The Two Core Wildcards
When using LIKE, we represent unknown characters with two specific symbols:
- The Percent Sign (
%): Represents zero, one, or multiple characters. Think of it as a "placeholder for anything." - The Underscore (
_): Represents exactly one single character.
Putting It Into Practice
Imagine you have a Customers table and you want to find everyone whose last name begins with "Rob". You don't know if the name is "Robbins," "Roberts," or "Robinson," so you use the percent sign to tell the database: "Find anything that starts with 'Rob' followed by any number of characters."
SELECT first_name, last_name
FROM Customers
WHERE last_name LIKE 'Rob%';
If you wanted to find a name that is exactly four letters long and starts with "Jo" (like "John" or "Joan"), you would use the underscore to demand two more specific characters:
SELECT first_name
FROM Customers
WHERE first_name LIKE 'Jo__';
Why This Matters
Pattern matching is the bridge between rigid data storage and human interaction. By using LIKE with wildcards, you transform your SELECT statements from simple lookups into powerful search tools. You are no longer limited to finding what you know is there; you can now explore your data to find patterns you weren't even sure existed.
Key Takeaway: Use the LIKE operator paired with the % wildcard for variable-length matches and the _ wildcard for fixed-length character positions to query data when your search criteria is only partially known.