Master the SQL LIKE Operator with Real Examples and Tips

Need to find partial matches or search patterns in SQL? The LIKE operator is the tool for the job. It's your go-to for string-based filtering, offering flexible ways to search using wildcards. In this guide, we’ll cover the syntax, examples, and best practices to help you use LIKE effectively.

How SQL LIKE Works

The LIKE operator compares a column value to a specified pattern using wildcards. Most commonly:

• % matches any number of characters

• _ matches a single character

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

Finds all names starting with "J".

Real-World Examples

1. Starts With:

SELECT * FROM cities WHERE name LIKE 'San%';

2. Ends With:

SELECT * FROM emails WHERE address LIKE '%@gmail.com';

3. Contains:

SELECT * FROM articles WHERE title LIKE '%SQL%';

4. Specific Length:

SELECT * FROM codes WHERE value LIKE '____';

5. Combined Pattern:

SELECT * FROM users WHERE username LIKE 'A___%';

Best Practices

• Avoid leading wildcards (%abc) to allow index usage

• Use targeted patterns for better performance

• Combine with LENGTH() or SUBSTRING() for more control

• Be mindful of case sensitivity and collation settings

• Consider indexes and EXPLAIN plans when optimizing queries

FAQ

Can I use LIKE with multiple patterns?

Yes, using OR, or in PostgreSQL, with LIKE ANY.

Does LIKE work on numbers?

Yes, if they can be cast to strings.

Is LIKE case-sensitive?

Usually, yes. But it depends on the collation. Use LOWER() to normalize if needed.

Is regex supported in LIKE?

No. For regex, use SIMILAR TO or database-specific regex functions.

How do I avoid performance issues with LIKE?

Don’t start patterns with %. Add indexes and avoid unnecessary wildcards.

Conclusion

The LIKE operator is essential for flexible string matching in SQL. From filtering emails to finding usernames, it’s a powerful tool when used wisely. For better results, combine LIKE with indexing and explore its behavior in your database engine.

To test and fine-tune pattern-matching queries easily, try out DbVisualizer—a powerful client that makes SQL development intuitive across all major databases.

Read A Complete Guide to the SQL LIKE Operator for more info.