A Complete Guide to the MySQL LIKE Operator

Finding Needles in Database Haystacks

Searching through text data in databases is like trying to find a specific book in a massive library. MySQL’s LIKE operator acts as your trusty librarian, helping you locate data patterns when you only remember fragments of information. Whether you’re searching for usernames that start with “admin”, products containing “deluxe” in their description, or phone numbers matching a particular area code, LIKE gives you the pattern-matching power you need.

Unlike exact-match comparisons with =, the LIKE operator lets you use wildcards to search for flexible patterns. It’s one of those essential tools that turns basic data retrieval into intelligent searching. Let’s explore how to master this operator for your database queries.

Understanding the LIKE Basics

The LIKE operator works with two special wildcard characters:

% - Matches any sequence of characters (including none)
_ - Matches exactly one character

Basic syntax:

SELECT columns FROM table WHERE column LIKE pattern;

Everyday examples:

-- Find names starting with 'J'
SELECT * FROM employees WHERE name LIKE 'J%';

-- Find 4-letter words ending with 'n'
SELECT * FROM words WHERE word LIKE '___n';

-- Find products containing 'pro' anywhere
SELECT * FROM products WHERE description LIKE '%pro%';

The Power of Wildcards

Wildcards transform LIKE from a simple matcher into a powerful search tool:

Starts-with searches:

-- All domain emails
SELECT * FROM users WHERE email LIKE '%@company.com';

Ends-with searches:

-- JPEG images
SELECT * FROM assets WHERE filename LIKE '%.jpg';

Position-specific patterns:

-- Area codes starting with 212
SELECT * FROM contacts WHERE phone LIKE '212___%';

Mixed character matching:

-- Find 'data1' through 'data9'
SELECT * FROM records WHERE record_name LIKE 'data_';

Case Sensitivity Considerations

The behavior of LIKE depends on your collation settings:

-- Case-insensitive search (common default)
SELECT * FROM products WHERE name LIKE '%widget%';

-- Force case-sensitive search
SELECT * FROM products WHERE name LIKE BINARY '%Widget%';

Always verify your collation settings if case-sensitivity matters in your searches.

Practical Pattern Matching Scenarios

Customer Data Searches

-- Find customers with last names starting with 'Mc' or 'Mac'
SELECT * FROM customers
WHERE last_name LIKE 'Mc%' OR last_name LIKE 'Mac%';

Product Catalog Filtering

-- Find premium products (marked with -PRM at end)
SELECT * FROM inventory
WHERE sku LIKE '%-PRM' AND stock_quantity > 0;

Data Validation

-- Find potentially malformed email addresses
SELECT * FROM subscribers
WHERE email NOT LIKE '%@%.%';

Performance Implications

While incredibly useful, LIKE can be resource-intensive:

Leading wildcards (LIKE '%term') prevent index usage
Consider full-text search for large text columns
For fixed patterns, use = or LEFT()/RIGHT() instead

Optimization example:

-- Slow with leading %
SELECT * FROM logs WHERE message LIKE '%error%';

-- Faster alternative for some cases
SELECT * FROM logs WHERE INSTR(message, 'error') > 0;

Advanced LIKE Techniques

Escaping Special Characters

When searching for literal % or _:

-- Find discounts with percentage signs
SELECT * FROM promotions
WHERE description LIKE '%\%%' ESCAPE '\';

Combining with Other Operators

-- Recent high-priority tickets
SELECT * FROM support_tickets
WHERE priority LIKE 'urgent%'
  AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

Using with CONCAT

-- Dynamic pattern building
SET @search_term = 'son';
SELECT * FROM products
WHERE name LIKE CONCAT('%', @search_term, '%');

Alternatives to LIKE

When LIKE isn’t the right tool:

Use REGEXP for complex pattern matching
Consider full-text indexes for large text searches
Use INSTR() for simple substring searches
Try SOUNDEX() for phonetic matching

Common Pitfalls to Avoid

Overusing leading wildcards that kill performance
Forgetting about case sensitivity
Not escaping special characters when needed
Using for exact matches when = would suffice
Neglecting to trim whitespace before matching

Summary

The MySQL LIKE operator is your go-to tool for flexible pattern matching in text data. From simple prefix searches to complex wildcard patterns, it provides the searching capabilities that make databases truly useful in real-world applications. While it’s not without its performance considerations (especially with leading wildcards), when used judiciously it can solve a wide variety of text search problems.

Remember that LIKE is just one tool in your text-searching toolbox. For simple patterns it’s perfect, but as your needs grow more complex, explore alternatives like regular expressions or full-text search. With the techniques covered here, you’re now equipped to implement efficient, effective text searches in your MySQL databases.