SQL LIKE operator

SQL Tutorial

Here is a clean and practical guide to the SQL LIKE operator, with patterns, examples, and best practices for different SQL engines.

 What the SQL LIKE operator Does

LIKE is used in SQL to match text patterns using wildcards.

It is commonly used in WHERE clauses to perform partial string searches.

 Wildcards Used with LIKE

WildcardMeaning
%Matches zero or more characters
_Matches exactly one character

 Basic Examples

1. Starts with

2. Ends with

3. Contains

4. Single-character match

 Case Sensitivity Notes

  • MySQL: LIKE is case-insensitive in most collations

  • PostgreSQL: LIKE is case-sensitive

    • Use ILIKE for case-insensitive matching

 Escape Special Characters

To search for % or _, use ESCAPE:

 LIKE with NOT

 Performance Tips

  •  Use indexed columns when possible
  •  Avoid leading % (e.g., %text%) if you want index usage
  •  Consider FULL TEXT SEARCH for complex matching
  •  Use ILIKE or lower() for case-insensitive search in PostgreSQL

 Real-World Use Cases

Find customers whose phone numbers contain “555”

Find products whose SKU begins with “AB”

Find emails ending with .org

You may also like...