The LIKE operator in SQL is used to search for a specified pattern in a column. It’s very useful when you want to filter records based on partial matches rather than exact values.
The most common wildcards used with LIKE are:
%→ represents zero or more characters_→ represents exactly one character
For example, suppose we have a customers table:
| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Alisha |
| 3 | Bob |
- To find all customers whose names start with “Ali”:
SELECT *
FROM customers
WHERE name LIKE 'Ali%';
Result: Alice, Alisha
- To find all names that end with “ce”:
SELECT *
FROM customers
WHERE name LIKE '%ce';
Result: Alice
- To find all names where the second character is “l”:
SELECT *
FROM customers
WHERE name LIKE '_l%';
Result: Alice, Alisha
In a real-world scenario, I used LIKE when filtering email addresses for marketing campaigns. For example, to select all Gmail users:
SELECT *
FROM customers
WHERE email LIKE '%@gmail.com';
This helped segment users efficiently without needing exact matches for every email address.
A challenge I faced with LIKE was performance — using patterns starting with % (e.g., %abc) prevents the database from using indexes, making the query slower on large tables. To improve performance, I either:
- Indexed computed columns if supported, or
- Avoided leading
%when possible and filtered differently.
A limitation is that LIKE is case-sensitive in some databases (like PostgreSQL) but not in others (like MySQL). For case-insensitive searches, I sometimes used ILIKE in PostgreSQL or applied UPPER()/LOWER() functions.
As an alternative, for more complex pattern matching or performance-critical cases, I’ve used regular expressions (REGEXP) where supported.
In summary — the LIKE operator is a simple yet powerful tool for pattern-based filtering, particularly useful in text searches, partial matches, and flexible reporting scenarios.