Pattern Matching with LIKE and Wildcards

Understanding Wildcard Filtering

In previous lessons, you learned how to filter data by evaluating columns against exact, known values. You used operators to check if a price was exactly 10.00, or if a category was exactly 'Electronics'. However, there are times when you need to filter data based on partial information. You might want to find all customers whose email addresses end in a specific domain, or locate a product when you only remember a portion of its name.

To perform these types of partial text searches, SQL provides wildcards. Wildcards are special characters used to match parts of a value. By combining wildcards with specific text strings, you construct search patterns that the database engine can use to find matching records.

The LIKE Operator

Wildcards are evaluated using the LIKE operator. When you use the LIKE operator in a WHERE clause, you are instructing the database management system that the following search pattern should be used for a pattern match, rather than a strict equality test.

SELECT product_name, price
FROM products
WHERE product_name LIKE 'Keyboard%';

In standard SQL, wildcards can only be used with text fields (character data types). You cannot use wildcards to search fields containing non-text data types, such as integers or decimals.

The Percent Sign Wildcard

The most frequently used wildcard is the percent sign (%). In a search pattern, the percent sign represents any sequence of zero, one, or multiple characters.

If you want to find all products that start with the word 'Fish', you place the percent sign at the end of the string.

SELECT product_name, category
FROM products
WHERE product_name LIKE 'Fish%';

This query retrieves 'Fish bowl', 'Fish tank', and 'Fishbowl filter'. The database matches the literal characters 'Fish' and then accepts any subsequent characters.

Wildcards can be placed anywhere within the search pattern. Placing a percent sign at both ends of a string allows you to search for a specific word located anywhere within the column.

SELECT product_name, description
FROM products
WHERE description LIKE '%toy%';

This is highly useful for searching through long descriptions or notes. Note that while the percent sign matches almost anything, it will never match a NULL value.

The Underscore Wildcard

The second standard wildcard is the underscore (_). While the percent sign matches any number of characters, the underscore wildcard acts as a strict placeholder for exactly one character.

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '_ inch teddy bear';

In this example, the search pattern will match '8 inch teddy bear' or '9 inch teddy bear'. However, it will not match '12 inch teddy bear' because the underscore only allows for a single character substitution, and '12' consists of two characters. If you need to match exactly two unknown characters, you must use two consecutive underscores (__).

Best Practices for Wildcards

While wildcard filtering is an incredibly powerful tool, it comes with a performance cost. Searching for wildcards takes the database engine significantly longer than processing standard relational comparisons. To maintain database efficiency, you should observe a few fundamental rules.

• Do not overuse wildcards. If you can achieve the exact same result using another operator, such as = or >=, use that operator instead.
• Avoid placing wildcards at the very beginning of a search pattern whenever possible. A search pattern like '%water' forces the database system to evaluate every single row in the table, which drastically slows down performance.
• Pay careful attention to the placement of your wildcards. A misplaced wildcard will return unintended data.

Apply these pattern matching techniques to explore your text data and uncover partial matches within your tables.