SQL Patterns
LIKE Wildcards for Pattern Matching
Pattern matching is essential for flexible data searching when exact matches aren't enough.
- % Wildcard β matches zero or more characters in a string
- _ Wildcard β matches exactly one single character Master flexible searching across your data.
What is the LIKE Operator?
DfLIKE Operator
The LIKE operator is a pattern matching predicate used in SQL WHERE clauses to search for a specified pattern in a column. It works with two special wildcard characters: % (percent) and _ (underscore).
-- Find all customers whose names start with 'J'
SELECT first_name, last_name
FROM customers
WHERE first_name LIKE 'J%';
-- Find all products with 'phone' anywhere in the name
SELECT product_name
FROM products
WHERE product_name LIKE '%phone%';
-- Find all emails ending with '.com'
SELECT email
FROM users
WHERE email LIKE '%.com';
Wildcard Characters
DfWildcard Characters
SQL provides two primary wildcards: % represents zero, one, or multiple characters, and _ represents exactly one character. Together they enable powerful pattern-based queries.
| Wildcard | Description | Example | Matches |
|---|---|---|---|
| % | Zero or more characters | 'A%' | Apple, A, ABC |
| _ | Exactly one character | 'A_' | AB, A1, Ax |
| [abc] | Any character in set | '[AE]%' | Apple, Egg |
| [^abc] | Any character NOT in set | '[^A]%' | Banana, Cherry |
| [a-z] | Range of characters | '[a-c]%' | Apple, Banana |
-- Find all employees whose names start with 'A'
SELECT first_name
FROM employees
WHERE first_name LIKE 'A%';
-- Find products with exactly 5 characters
SELECT product_code
FROM products
WHERE product_code LIKE '_____';
-- Find names starting with 'S' followed by any two characters
SELECT customer_name
FROM customers
WHERE customer_name LIKE 'S__%';
Combining LIKE with Other Operators
Combine LIKE with AND/OR for complex pattern searches. You can also use NOT LIKE to exclude patterns.
-- Find customers whose email starts with 'info' and ends with '.org'
SELECT email
FROM contacts
WHERE email LIKE 'info%org';
-- Find employees NOT in departments starting with 'S'
SELECT first_name, last_name
FROM employees
WHERE department LIKE 'S%' AND salary > 50000;
-- Find products matching pattern OR price threshold
SELECT product_name, price
FROM products
WHERE product_name LIKE '%pro%' OR price > 100;
Pattern Matching Examples
| Pattern | Description | Sample Match | Sample Non-Match |
|---|---|---|---|
| 'J%' | Starts with J | James | Adam |
| '%son' | Ends with son | Johnson | Smith |
| '%art%' | Contains art | Arthur | Baker |
| '_at' | 3 chars ending in cat | Cat | Bat |
| '___' | Exactly 3 chars | Bob | David |
-- Find all order numbers containing '2024'
SELECT order_id, order_date
FROM orders
WHERE order_id LIKE '%2024%';
-- Find phone numbers with country code +1
SELECT phone_number
FROM contacts
WHERE phone_number LIKE '+1%';
-- Find product codes starting with 'PROD' followed by digits
SELECT product_code
FROM inventory
WHERE product_code LIKE 'PROD____%';
LIKE queries cannot use standard indexes efficiently. For large tables, consider full-text search or specialized indexing for better performance.
Key Takeaways
- The LIKE operator enables flexible pattern matching in SQL queries
- % matches zero or more characters; _ matches exactly one character
- Use NOT LIKE to exclude patterns from results
- LIKE queries may not use indexes efficiently on large datasets
- Combine LIKE with AND/OR for complex filtering logic