In this lesson, you will learn how to locate specific information in text fields by searching for patterns at the beginning or end of a string.
Searching for Patterns with LIKE
When we filter data using basic comparisons like =, we look for exact matches. However, real-world data is often messy or semi-structured. To find values that start with or end with a specific sequence, we use the LIKE operator combined with the percent sign (%) wildcard. The % acts as a placeholder that represents "zero or more characters."
Matching Prefixes
If you want to find all customers whose email address starts with "admin", you place the wildcard at the end of your string.
SELECT email
FROM users
WHERE email LIKE 'admin%';
This query tells the database: "Give me every row where the email starts with exactly 'admin', followed by anything else (or nothing at all)."
Matching Suffixes
Conversely, if you want to find rows based on a specific ending, you place the wildcard at the beginning of the string. For example, if you need to find all records that end with the ".org" domain:
SELECT website
FROM companies
WHERE website LIKE '%.org';
Here, the % acts as a bridge, telling the database to ignore whatever comes before the ".org" suffix.
Why This Matters
This technique is essential for semi-structured data. Imagine a column containing product codes like PROD-100, PROD-200, or SALE-100. If you wanted to isolate all "PROD" items, you could simply use LIKE 'PROD%'. This allows you to slice through datasets without needing to know the exact full content of every field, provided you know the specific anchor point of the string.
Keep in mind that while LIKE is powerful for basic pattern matching, it can be slower on very large datasets compared to exact matches, as the database must scan the text to identify the requested pattern.
Key Takeaway: Use the LIKE operator with the % wildcard to perform prefix matching by placing the wildcard at the end, or suffix matching by placing it at the beginning.