Pattern Matching in PostgreSQL: Mastering LIKE and Wildcards

Previously in this course, we covered string manipulation functions to format and clean text data. While functions like UPPER() or SUBSTRING() are great for modifying data, they don't help you find records when you only have part of a search term. In this lesson, we introduce pattern matching to build flexible, user-friendly search capabilities.

Understanding the LIKE Operator

When you need to find rows where a column matches a specific pattern—rather than an exact value—you use the LIKE operator. Unlike the equality operator (=) which checks for an exact match, LIKE allows you to define "fuzzy" criteria.

To create these patterns, PostgreSQL provides two primary wildcards:

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

Using Wildcards in Search Queries

Let’s apply this to our store project. Suppose a customer enters "Jo" into the search bar, and you want to find all customers whose first names start with "Jo" (like "John", "Joe", or "Jolene").

SQL
SELECT first_name, last_name 
FROM customers 
WHERE first_name LIKE 'Jo%';

The % at the end tells PostgreSQL: "Find any string that starts with 'Jo' followed by anything else." If you wanted to find names that contain "an", you would wrap the sequence in wildcards: LIKE '%an%'.

Matching Specific Lengths with Underscores

Sometimes you need more precision. If you are searching for a product code or a short abbreviation where the character count is fixed, use the underscore:

SQL
-- Finds codes like 'A123', 'B123', but not 'AB123'
SELECT * 
FROM product_skus 
WHERE sku_code LIKE '_123';

Performing Case-Insensitive Searching

Standard LIKE is case-sensitive, which can frustrate users who type in lowercase. To perform a case-insensitive search, use the ILIKE operator (stands for "insensitive LIKE").

SQL
-- This will match 'John', 'john', and 'JOHN'
SELECT first_name, last_name 
FROM customers 
WHERE first_name ILIKE 'jo%';

ILIKE is a PostgreSQL-specific extension that saves you from manually applying UPPER() or LOWER() functions to both sides of the comparison, keeping your query code clean and readable.

Worked Example: Building a Search Feature

Imagine your store needs a search function for the products table. We want to find all items that contain "phone" in their name, regardless of casing.

SQL
-- Find all variations of 'phone' in product names
SELECT product_name, price 
FROM products 
WHERE product_name ILIKE '%phone%';

Hands-on Exercise

Open your psql terminal or pgAdmin query tool and try the following:

1. Find all customers whose email address ends in @gmail.com.
2. Find all products where the product name has exactly 5 characters and starts with "Smart". (Hint: Use underscores for the remaining characters).

Common Pitfalls

• Performance: While LIKE is powerful, using a leading wildcard (e.g., '%phone%') prevents PostgreSQL from using standard indexes effectively. This can cause queries to become slow as your database grows into millions of rows.
• Trailing Spaces: If your text data has hidden trailing spaces (e.g., 'Phone '), a LIKE search might fail to match it. Always ensure your data is cleaned during ingestion.
• Forgetting ILIKE: Beginners often stick to LIKE and wonder why their searches return zero results; always check your casing or switch to ILIKE.

FAQ

Can I use multiple wildcards in one string? Yes. You can combine them, such as LIKE 'J%n_s' to find names starting with J, ending with s, and having a specific character structure.

Is there a faster way to search text? For simple partial matches, ILIKE is fine. For professional-grade search engines, we will look at Full-Text Search Basics later in the course.

Does LIKE work on numbers? Technically, you can cast numbers to text to use LIKE, but it is generally an anti-pattern. Stick to numeric operators for actual numbers.

Recap

We've moved beyond exact matches by using LIKE and ILIKE to create flexible search patterns. By leveraging the % and _ wildcards, you can now provide intuitive search functionality for your store's customers.

Up next: We will dive into Boolean Logic in Queries to handle complex conditional filtering.