SQL LIKE Pattern Matching

What is LIKE?

The LIKE operator performs pattern matching on text columns. It uses two wildcards:

• % — Matches any sequence of characters (including none).
• _ — Matches exactly one character.

Syntax

SELECT * FROM table_name
WHERE column LIKE pattern;

PostgreSQL also offers ILIKE for case-insensitive matching.

When to Use

• Searching for names starting with a letter
• Finding emails from a specific domain
• Matching partial strings in a search feature

Key Points

1. Case Sensitivity — LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive matching.
2. Leading Wildcards — WHERE name LIKE '%son' cannot use a standard index. Consider a trigram index for better performance.
3. Escape Character — Use ESCAPE to search for literal % or _.
4. NOT LIKE — Negates the pattern: WHERE name NOT LIKE 'A%'.
5. Regex Alternative — PostgreSQL supports ~ for full regex matching when LIKE is not powerful enough.