SQL LIKE and Pattern Matching: Complete Guide with Interactive Examples

Searching for text within your database is one of the most common operations you’ll perform. Whether you’re looking for customer names, filtering product descriptions, or finding records that match a pattern, SQL provides powerful tools for text searching. The LIKE operator is your primary tool for pattern matching in SQL.

Throughout this guide, you’ll find interactive SQL exercises that let you practice pattern matching in your browser—no database setup required. Write queries, get instant feedback, and build your skills with real examples.

Understanding SQL Pattern Matching

SQL doesn’t have a dedicated “contains” function like some programming languages. Instead, it uses the LIKE operator combined with wildcard characters to search for patterns within text. This approach is both powerful and flexible, allowing you to match anything from simple substrings to complex patterns.

The basic syntax is:

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

The Two SQL Wildcards

SQL provides two wildcard characters for pattern matching:

Understanding these wildcards is the key to mastering SQL text searches.

Searching for Text That Contains a Substring

The most common text search is checking if a column contains a specific word or phrase. Use the % wildcard on both sides of your search term:

SELECT * FROM customers
WHERE company_name LIKE '%Tech%';

This query finds all customers where company_name contains “Tech” anywhere—at the beginning, middle, or end. It would match:

• “Tech Solutions Inc”
• “FinTech Corp”
• “Technology Partners”

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Pattern Positions: Starts With, Ends With, Contains

You can control where the pattern appears by positioning your wildcards:

Starts With (Prefix Search)

SELECT * FROM products
WHERE name LIKE 'iPhone%';

Matches: “iPhone 15”, “iPhone Pro Max”
Does not match: “New iPhone Case”

Ends With (Suffix Search)

SELECT * FROM files
WHERE filename LIKE '%.pdf';

Matches: “report.pdf”, “invoice.pdf”
Does not match: “pdf_guide.txt”

Contains (Anywhere)

SELECT * FROM messages
WHERE content LIKE '%urgent%';

Matches anywhere the word “urgent” appears.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Using Underscore for Precise Matching

The underscore wildcard (_) matches exactly one character, which is useful when you know the pattern’s structure:

SELECT * FROM products
WHERE product_code LIKE 'A__-___';

This matches codes like “A12-345” or “ABC-XYZ” but not “A1-234” or “ABCD-123”.

Common use cases for underscore:

• Matching fixed-format codes (SKUs, part numbers)
• Finding values with specific character positions
• Validating data formats

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Case Sensitivity in Pattern Matching

LIKE behavior varies by database:

To ensure consistent case-insensitive searches, use the LOWER() or UPPER() function:

SELECT * FROM customers
WHERE LOWER(name) LIKE '%smith%';

This finds “Smith”, “SMITH”, “smith”, and “SmItH”.

PostgreSQL alternative: Use ILIKE for case-insensitive matching:

SELECT * FROM customers
WHERE name ILIKE '%smith%';

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Escaping Special Characters

What if you need to search for actual % or _ characters? Use the ESCAPE clause:

SELECT * FROM discounts
WHERE description LIKE '%50\%%' ESCAPE '\';

This searches for “50%” literally. The backslash tells SQL to treat the following % as a regular character, not a wildcard.

Common scenarios:

• Searching for percentage values (“50%”, “25% off”)
• Finding filenames with underscores (“report_2024”)
• Matching URLs or file paths

NOT LIKE: Excluding Patterns

Use NOT LIKE to find records that don’t match a pattern:

SELECT * FROM users
WHERE email NOT LIKE '%@gmail.com';

This finds all users who don’t have Gmail addresses. Combine with other conditions for more complex filtering:

SELECT * FROM products
WHERE name NOT LIKE '%discontinued%'
  AND name NOT LIKE '%legacy%';

Combining Multiple Patterns with OR

Search for multiple patterns using OR:

SELECT * FROM products
WHERE name LIKE '%laptop%'
   OR name LIKE '%notebook%'
   OR name LIKE '%computer%';

This finds products matching any of these terms.

Performance Considerations

Pattern matching can be slow on large tables, especially with leading wildcards:

Slow (Cannot Use Index)

SELECT * FROM products
WHERE name LIKE '%widget%';  -- Leading wildcard

The database must scan every row because it can’t predict where “widget” might appear.

Fast (Can Use Index)

SELECT * FROM products
WHERE name LIKE 'widget%';  -- No leading wildcard

When the pattern starts with literal characters, the database can use an index to quickly find matching rows.

Optimization Tips

1. Avoid leading wildcards when possible
2. Create indexes on frequently searched columns
3. Consider full-text search for complex text searching needs
4. Limit result sets with additional WHERE conditions

SELECT * FROM products
WHERE category = 'Electronics'  -- Filter first
  AND name LIKE '%pro%';        -- Then pattern match

Alternative: Full-Text Search

For heavy text searching needs, consider full-text search capabilities:

PostgreSQL:

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('database & performance');

MySQL:

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database performance');

Full-text search offers:

• Better performance on large text fields
• Relevance ranking
• Word stemming (finding “running” when searching “run”)
• Stop word handling

Common Patterns Quick Reference

Working with Pattern Matching in Beekeeper Studio

When building complex LIKE queries, having a good SQL editor makes development much easier. Beekeeper Studio provides helpful features for working with pattern matching:

• Autocomplete: Suggests column names to avoid typos in your WHERE clauses
• Query formatting: Keeps complex multi-pattern queries readable
• Result highlighting: Easily spot matched patterns in your results
• Multi-database support: Consistent experience across PostgreSQL, MySQL, SQLite, and more

The free version includes everything you need for mastering SQL pattern matching.

Key Takeaways

SQL pattern matching with LIKE is essential for searching text in your database. Here’s what to remember:

• Use % to match any characters (zero or more) - this is your “contains” search
• Use _ to match exactly one character - great for fixed-format patterns
• Position wildcards strategically: %word (ends with), word% (starts with), %word% (contains)
• Handle case sensitivity with LOWER() or database-specific functions like ILIKE
• Escape special characters when searching for literal % or _
• Avoid leading wildcards when possible for better performance
• Consider full-text search for complex text searching needs

Pattern matching is a fundamental SQL skill. Whether you’re filtering customer data, searching logs, or building search functionality, the LIKE operator gives you the flexibility to find exactly what you need.

Ready to practice more? Try the interactive examples above, or explore more SQL tutorials to continue building your database skills.