SQL LIKE – Pattern Matching with Wildcards in SQL
Introduction – Why Use SQL LIKE?
The LIKE operator in SQL is used to search for specific patterns in string values. It’s a powerful tool for performing partial matches, flexible filtering, and simple text analysis.
In this guide, you’ll learn:
- How to use
LIKEwith%and_wildcards - Combine
LIKEwithWHEREand logical conditions - Handle case-sensitivity across different databases
- Real-world examples of pattern matching
1. Basic LIKE Syntax
SELECT * FROM users WHERE name LIKE 'A%';
Returns names starting with “A”.
2. Pattern Matching Wildcards
| Symbol | Meaning | Example | Matches |
|---|---|---|---|
% | Zero or more characters | 'J%' | John, Jake, Jasmine |
_ | Exactly one character | 'J_n' | Jon, Jan, Jim |
3. Examples of SQL LIKE Patterns
-- Names that start with A
SELECT * FROM employees WHERE name LIKE 'A%';
-- Emails ending with '.com'
SELECT * FROM contacts WHERE email LIKE '%.com';
-- Cities with 'new' in the middle
SELECT * FROM locations WHERE city LIKE '%new%';
-- Postal codes that are exactly 5 digits
SELECT * FROM addresses WHERE postal_code LIKE '_____' -- five underscores
4. Using NOT LIKE
SELECT * FROM products WHERE name NOT LIKE '%apple%';
Returns all products that do not contain “apple”.
5. Case Sensitivity in LIKE
- MySQL: Case-insensitive by default (unless BINARY keyword used)
- PostgreSQL: Case-sensitive by default
- SQL Server: Depends on the collation
Use ILIKE in PostgreSQL for case-insensitive matching.
6. LIKE with Logical Operators
SELECT * FROM users
WHERE name LIKE 'A%' OR name LIKE 'B%';
Combines multiple LIKE patterns using OR.
Best Practices
| Do This | Avoid This |
|---|---|
Use % and _ precisely | Overusing % at both ends for large tables |
Use ILIKE in PostgreSQL if needed | Assuming all LIKE queries are case-insensitive |
| Optimize indexed columns | Ignoring performance impact of wildcards |
Summary – Recap & Next Steps
SQL LIKE is your go-to tool for performing text pattern matching. With % and _, you can construct expressive search filters across a wide variety of string data.
Key Takeaways:
%matches any number of characters;_matches exactly one- Combine LIKE with
WHERE,NOT, andORfor flexible searches - Behavior varies based on RDBMS case sensitivity settings
Real-World Relevance:
Used in search bars, product filters, customer lookup, and data cleansing operations.
FAQ – SQL LIKE
What is the difference between LIKE and =?
LIKE supports pattern matching; = checks for an exact match.
Does LIKE support case-insensitive search?
Depends on your database engine. Use ILIKE for PostgreSQL.
Can I use LIKE with numbers?
Yes, but numbers are implicitly cast as strings.
Can I match only one character?
Use _ (underscore) to match exactly one character.
What is the performance impact of LIKE?
It can be slow with leading % wildcards unless indexed or optimized.
Share Now :
