Pattern Matching with LIKE

By the end of this lesson, you will be able to filter text data in SQL using the LIKE operator and wildcard characters to find patterns rather than exact matches.

What it is

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Unlike the equality operator (=), which requires an exact match, LIKE allows for partial matching using wildcards. The two most common wildcards are:

• % (Percent Sign): Represents zero, one, or multiple characters.
• _ (Underscore): Represents exactly one character.

This technique is essential for fuzzy searching, such as finding all email addresses ending in a specific domain or identifying product codes with a certain prefix.

Why it matters

• User-Friendly Search: Allows users to find records even if they don't know the full value (e.g., searching for "Smith" finds "Johnson-Smith").
• Data Cleaning: Helps identify inconsistent formatting, such as phone numbers missing area codes.
• Pattern Analysis: Enables quick grouping of data based on prefixes or suffixes without complex regular expressions.
• Performance Balance: While not always indexed efficiently, it is often faster and simpler than writing custom functions for basic string checks.

Syntax or steps

The basic syntax places the LIKE keyword between the column name and the pattern string. The pattern must be enclosed in single quotes.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

To construct a pattern:

1. Start with the literal text you want to match.
2. Add % before or after the text to allow variable length.
3. Add _ where you expect exactly one unknown character.

Example

Suppose we have a table named customers with columns id, name, and email. We want to find customers whose names start with "J" and those whose emails are from the "gmail.com" domain.

SELECT id, name, email
FROM customers
WHERE name LIKE 'J%' 
   OR email LIKE '%@gmail.com';

Part-by-part explanation:

• name LIKE 'J%': Matches any name starting with "J". The % accepts any number of subsequent characters (including none).
• OR: Combines the conditions so either match returns the row.
• email LIKE '%@gmail.com': Matches any email ending with "@gmail.com". The leading % ignores whatever comes before the domain.

Common mistakes

• Case Sensitivity: In some databases (like PostgreSQL), LIKE is case-sensitive. Use ILIKE (PostgreSQL) or wrap both sides in UPPER() (MySQL/SQL Server) if you need case-insensitive matching.
• Leading Wildcards Kill Indexes: Patterns like '%text' force a full table scan because the database cannot use an index to jump to the start of the string. Prefer 'text%' when possible.
• Confusing % and _: Using 'J_' only matches three-letter names starting with J (e.g., "Jim"), whereas 'J%' matches "John", "James", etc.
• Escaping Special Characters: If your data contains actual percent signs or underscores that should be treated as literals, you must escape them using the ESCAPE clause.

When to use it

Compare LIKE with Regular Expressions (REGEXP or RLIKE) and Exact Matching (=).

Use LIKE for straightforward substring searches. Switch to REGEXP only when LIKE becomes too verbose or impossible to express.

Practice

Guided Exercise: Write a query to find all products in a products table where the sku starts with "A1" and ends with "9".

Hint: Combine the prefix and suffix patterns: 'A1%9'.

Challenge: Find all employees whose last name has exactly 5 letters and starts with "S".

Hint: Use 'S____' (one S followed by four underscores).

Quick check

Question: What does the pattern '_a%' match?

Answer: It matches any string where the second character is "a". The first underscore represents any single character, and the percent sign represents any remaining sequence.

Summary

The LIKE operator provides a simple yet powerful way to perform pattern-based filtering in SQL using % and _ wildcards. Mastering these symbols allows for flexible data retrieval while keeping queries readable and efficient for common search tasks.