Skip to main content
In this tutorial, we will work with a products database to demonstrate how to use SQL pattern matching with the LIKE operator. We will begin by retrieving all records from the database, add new entries, and then explore filtering records using various LIKE patterns. This guide is perfect for developers and database administrators seeking to enhance their SQL querying skills.

​
Retrieving and Adding Records

Start by retrieving all products to check the current state of the database:
Next, insert some TV items with descriptive names. For example, add products named “TV BLUE”, “TV RED”, and “TV YELLOW” with designated prices—200 for one, a different value for another, and 50 for “TV YELLOW” (indicating less popularity). After inserting these entries, re-run the following query to verify your changes:
Ensure that you have the necessary permissions to insert and retrieve records from your products database.

​
Filtering Records Using the LIKE Operator

Standard comparison operators (such as equals, greater than, or less than) are not flexible enough for partial text matching. The LIKE operator, combined with wildcard characters, offers the ability to filter text columns—similar to regular expressions in Python.

​
Finding Products That Start with “TV”

To fetch products whose names start with “TV”, use the percent sign (%) as a wildcard representing any sequence of characters after “TV”. For example:
This query returns every product with a name beginning with “TV.”

​
Changing the Pattern for Different Searches

You can modify the pattern to search for products starting with any other letter. For example, to find products starting with “A”, use:
Similarly, to retrieve products starting with “R” (e.g., products like “Remote”), run:

​
Finding Products That End with a Specific Letter

To filter products ending with a particular letter, place the wildcard at the beginning of the pattern. For instance, to select names ending with “E”:
If no products end with the specified letter (say, “n”), the query returns an empty result set.

​
Excluding Certain Patterns

To exclude records that contain a specific substring, combine the NOT operator with LIKE. For example, to exclude rows containing ‘jené’, use:
An example result after running this query is presented in the table below:

​
Matching a Substring Anywhere in the Name

To match any product that contains a specific substring (for example, “EN”), enclose the substring with wildcards on both sides:
If you want to exclude rows containing a particular pattern—such as the substring “enn”—use the NOT operator:
If you encounter an error due to a possible typo or syntax issue, retyping the query (for example, updating ‘%jené%’ to ‘%enn%’) might resolve the problem.

​
Handling Unexpected Errors

During testing, you may run into unexpected errors. For instance, attempting the query below resulted in an error:
After identifying the issue, modify the query to search for a valid substring such as “pen”:
This corrected query returns the expected results.

​
Conclusion

The SQL LIKE operator is a powerful tool for pattern matching in text columns. Whether you are searching for records that start with a specific string, end with a particular character, or contain a substring anywhere within the text, the flexibility of wildcards can refine your queries effectively. Experiment with different patterns to fully harness the capabilities of SQL pattern matching and to understand potential pitfalls along the way. For further reading, check out the SQL documentation for more advanced querying techniques.

Watch Video