Pattern Matching with SQL LIKE

SQL provides powerful tools for searching and filtering data, and one of the most versatile tools is the LIKE operator. The LIKE operator is used for pattern matching within text data, allowing you to find records that match a specific format or string pattern. Whether you're looking for names that begin with certain letters, email addresses that follow a specific domain, or phone numbers in a particular format, LIKE is a critical tool for database querying.

This chapter will explain how to use the LIKE operator, from basic usage to advanced pattern matching techniques. You’ll learn how to use wildcards, combine LIKE with other conditions, and optimize your queries for performance.

Basic Syntax of SQL LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is most commonly used with string data types (e.g., VARCHAR, CHAR, TEXT).

Syntax:

				
					SELECT column_name 
FROM table_name
WHERE column_name LIKE pattern;
  • column_name: The column to search for the pattern.
  • table_name: The table containing the data.
  • pattern: The pattern to search for, which can include wildcards.

Wildcards Used with LIKE

Wildcards are characters that represent one or more characters in a string. The two primary wildcards used with LIKE are:

  • %: Represents zero or more characters.
  • _: Represents a single character.

Example 1: Using % Wildcard

The % wildcard allows you to match any sequence of characters. For example, to find all customers whose name starts with “A”:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE 'A%';

Output:

Name
Alice Smith
Aaron Brown
Amanda White

The % after “A” means any sequence of characters can follow the letter “A.”

Example 2: Using _ Wildcard

The _ wildcard matches exactly one character. For example, to find all customers whose name is exactly five characters long and starts with “J”:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE 'J____';

Output:

Name
Jason
Julie

The four underscores (____) represent four characters, so the query finds names that are exactly five characters long.

Using LIKE for Case-Insensitive Searches

In most databases, the LIKE operator is case-insensitive by default. This means that searching for LIKE 'A%' will return records where the name starts with both “A” and “a.”

Example 3: Case-Insensitive Search

To find all names starting with “a” or “A”:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE 'a%';

Output:

Name
Alice Smith
Aaron Brown
Amanda White

The query returns results regardless of whether the first letter is uppercase or lowercase.

Combining LIKE with Other Conditions

You can combine LIKE with other SQL clauses, such as AND, OR, and NOT, to create more complex search queries.

Example 4: Combining LIKE with AND

To find customers whose name starts with “A” and who live in “New York”:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE 'A%' AND city = 'New York';

Output:

Name
Alice Smith

This query filters results to include only customers whose name starts with “A” and who are from New York.

Example 5: Using NOT LIKE

The NOT LIKE operator is used to find records that do not match a specified pattern. For example, to find customers whose name does not start with “A”:

Query:

				
					SELECT name
FROM customers
WHERE name NOT LIKE 'A%';
;

Output:

Name
Brian Jones
Christine Lee

This query returns all names that do not start with “A.”

Using LIKE with Numbers

Though LIKE is typically used with text data, it can also be applied to numeric data stored as strings, such as phone numbers or zip codes.

Example 6: Finding Phone Numbers with Specific Patterns

Consider a customers table with a phone_number column. To find phone numbers that start with “555”:

Query:

				
					SELECT name, phone_number
FROM customers
WHERE phone_number LIKE '555%';

Output:

NamePhone Number
Alice Smith555-1234
Brian Jones555-5678

This query matches phone numbers that start with “555,” commonly used in fictional examples.

Advanced Pattern Matching Techniques

Example 7: Finding Names Ending with Specific Letters

To find all customers whose last names end with “son”:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE '%son';

Output:

Name
Jason Anderson
Mason Johnson

The % wildcard before “son” means any sequence of characters can come before “son.”

Example 8: Finding Names Containing a Specific Sequence

To find customers whose name contains the letters “an” anywhere in the name:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE '%an%';

Output:

Name
Amanda White
Daniel Clark

The % on both sides of “an” ensures that “an” can appear anywhere in the name.

Optimizing LIKE Queries

While the LIKE operator is a powerful tool for pattern matching, it can become slow on large datasets, especially when using the % wildcard at the beginning of the pattern. Here are a few tips to optimize LIKE queries:

  1. Use Indexed Columns: Make sure the column you’re searching with LIKE is indexed to improve performance.
  2. Avoid Leading Wildcards: Avoid using % at the beginning of the pattern, as it prevents the database from using an index efficiently.
  3. Consider Full-Text Search: For large text fields, consider using full-text search capabilities offered by some databases.

LIKE and Escape Characters

Sometimes, the search pattern may contain characters that are normally treated as wildcards (% or _). In such cases, you can use an escape character to treat them as literals.

Example 9: Escaping Wildcards

Suppose you want to search for names that contain the literal “%” character:

Query:

				
					SELECT name
FROM customers
WHERE name LIKE '%\%%' ESCAPE '\';

Output:

Name
50% Off Deals

The backslash (\) is used to escape the % wildcard, so it is treated as a literal character.

Using LIKE with Joins

You can combine LIKE with JOIN operations to search across multiple related tables.

Example 10: Searching Orders for Products

Consider two tables: customers and orders.

Customer IDName
1Alice Smith
2Brian Jones
Order IDCustomer IDProduct Name
1011Apple iPhone
1021Samsung TV
1032Sony Headphones

To find all orders for products containing the word “Apple”:

Query:

				
					SELECT customers.name, orders.product_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.product_name LIKE '%Apple%';

Output:

NameProduct Name
Alice SmithApple iPhone

The query joins the customers and orders tables, then searches for products containing “Apple” in the name.

Performance Considerations

  • Indexing: When using LIKE, especially for large datasets, make sure to index the columns you’re searching. However, keep in mind that indexes may not be used when the pattern starts with a wildcard (%).
  • Leading Wildcard Issues: Avoid patterns like %pattern, as they force a full table scan, slowing down the query.
  • Combining with Other Conditions: For better performance, combine LIKE with indexed columns, filters, or limiting results.

The SQL LIKE operator is an essential tool for pattern matching and searching within text data. Whether you're filtering names, phone numbers, email addresses, or product descriptions, LIKE allows you to define flexible search patterns with wildcards like % and _. In this chapter, we've covered the basics of LIKE, explored advanced usage like combining with AND and NOT, handling special cases with escape characters, and optimizing LIKE queries for performance. Happy coding !❤️

Table of Contents

Popular Tutorials

Contact here

Copyright © 2025 Diginode

Made with ❤️ in India

Home
Roadmap
ATS Resume
Playground
Resources