What is the proper syntax for LIKE?

Mastering SQL LIKE: Pattern Matching in Databases

Explore the LIKE operator in SQL for powerful pattern matching, including wildcards, common use cases, and performance considerations.

The SQL LIKE operator is a fundamental tool for searching for specified patterns within a column. Unlike direct equality comparisons, LIKE allows for flexible string matching using wildcard characters. This article will delve into the proper syntax, common wildcards, and practical applications of the LIKE operator, primarily focusing on MySQL, but the concepts are broadly applicable across most SQL databases.

Understanding the LIKE Operator and Wildcards

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It's case-insensitive by default in many SQL implementations (like MySQL on Windows, but case-sensitive on Linux/Unix unless a case-insensitive collation is used). The power of LIKE comes from its use of wildcard characters, which represent one or more characters in the search pattern.

There are two primary wildcard characters used with LIKE:

Common Wildcard Characters

The two most common wildcard characters are % and _.

graph TD
    A[Start Pattern Matching] --> B{Use LIKE Operator}
    B --> C{Specify Pattern String}
    C --> D{"Wildcard Characters?"}
    D -- Yes --> E1["%" - Matches zero or more characters]
    D -- Yes --> E2["_", - Matches exactly one character]
    D -- No --> F[Exact String Match (less common with LIKE)]
    E1 --> G[Result: Flexible Pattern Match]
    E2 --> G
    F --> G

Flowchart of SQL LIKE operator with wildcard usage

The Percent Sign (%)

The percent sign represents zero, one, or multiple characters. It's incredibly versatile for matching patterns at the beginning, middle, or end of a string.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'a%'; -- Finds any values that start with 'a'

SELECT column_name
FROM table_name
WHERE column_name LIKE '%a'; -- Finds any values that end with 'a'

SELECT column_name
FROM table_name
WHERE column_name LIKE '%or%'; -- Finds any values that have 'or' in any position

Examples of using the '%' wildcard

The Underscore (_)

The underscore represents a single character. This is useful when you know the exact length of the string or the position of a specific character.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'h_t'; -- Finds 'hot', 'hat', 'hit', etc.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'b__k'; -- Finds 'book', 'back', 'bank', etc. (exactly two characters between 'b' and 'k')

Examples of using the '_' wildcard

Combining Wildcards and Escaping

You can combine both % and _ wildcards in a single LIKE pattern for more complex searches. For instance, '_a%' would find any string that has 'a' as its second character.

SELECT product_name
FROM products
WHERE product_name LIKE 'L_mp%'; -- Finds 'Lamp', 'Limp', 'Lump sum', etc.

SELECT customer_name
FROM customers
WHERE customer_name LIKE '%_son'; -- Finds names ending with 'son' preceded by any single character, e.g., 'Johnson', 'Jackson'

Combining '%' and '_' wildcards

Escaping Wildcard Characters

What if your search pattern needs to include an actual % or _ character? You need to escape them. The ESCAPE clause allows you to define a character that will be used to escape the wildcards.

SELECT file_name
FROM documents
WHERE file_name LIKE '%10\% discount%' ESCAPE '\'; -- Finds '10% discount'

SELECT item_code
FROM inventory
WHERE item_code LIKE 'prefix\_suffix%' ESCAPE '\'; -- Finds 'prefix_suffix_item'

Using the ESCAPE clause to match literal wildcards

Performance Considerations and Best Practices

While LIKE is powerful, its usage can impact query performance. Understanding these implications and following best practices can help maintain efficient database operations.

1. Avoid Leading Wildcards: As mentioned, LIKE '%pattern' is generally inefficient because the database has to scan every row. If possible, use LIKE 'pattern%' which can utilize indexes.
2. Use Specific Patterns: The more specific your pattern, the faster the search will be. LIKE 'apple%' is faster than LIKE '%ple%'.
3. Consider Full-Text Search: For very large text fields or complex linguistic searches, consider using full-text search capabilities provided by your database (e.g., MySQL's MATCH AGAINST). These are optimized for text retrieval and can be much faster than LIKE for such scenarios.
4. Collation: Be aware of your database's collation settings. Collation determines character set and sorting rules, including case sensitivity. If you need case-insensitive matching on a case-sensitive collation, you might need to convert the column to lowercase or uppercase using functions like LOWER() or UPPER() before applying LIKE.

SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE 'apple%'; -- Ensures case-insensitive search regardless of collation

Ensuring case-insensitive LIKE search with LOWER() function