Build a Search Feature with SQL LIKE and Wildcards
How Wildcards Shape Your Queries
When users search for something in a database, exact matches aren’t always enough. Sometimes, you need to match partial strings, guess at what the user meant, or find entries that share a common prefix or pattern. That’s where SQL’s LIKE operator and its wildcards come in. These allow you to write flexible queries that can return a broader set of results while still staying fast and understandable.
How Pattern Matching Works with LIKE and Wildcards
Pattern matching in SQL is done through the LIKE operator, which compares a string value to a pattern that includes special wildcard characters. This type of comparison lets you find matches that aren’t exact. Instead of only returning results where the value is an exact match, the query checks for patterns made with % or _. These characters each have a specific behavior during matching. Getting familiar with how they work gives you better control over the search behavior and helps avoid unexpected results.
The Mechanics Behind %
The percent sign matches any number of characters, including none at all. You can use it to look for values that contain something inside them, or values that start or end with a certain string. Here’s a case where the query searches for product names that include the word pro somewhere in the text.
This will return matches such as Pro Series Headphones, Professional Tool Set, or GoPro Camera, because all of them contain pro. The match is done one character at a time, and when SQL sees %, it treats it as a placeholder that matches any length of text.
If the pattern starts with %, the database has no fixed point to begin narrowing the search, so it checks each value. That kind of scan is slower because it can’t take advantage of an index. But when the pattern begins with fixed text and ends with %, like this:
The database can often use an index, depending on how the table and columns were set up. In this case, the engine can look for rows that start with pro and scan forward from that position.
You can also use % in the middle to ignore part of the string between a known beginning and ending.
This matches values like ProSound Max and ProXMax, as long as the name starts with pro and ends with max, with anything in between. The percent sign matches as many characters as needed to satisfy the rest of the pattern.
The match is greedy. SQL doesn’t stop at the first possible fit. It keeps checking until it finds a string that fits the entire pattern.
The Underscore For Single Characters
The underscore wildcard matches exactly one character. It’s useful when the structure of the string is predictable but the content varies. For example, if serial numbers follow a pattern and you want to find values that begin with AC, followed by one character, and then end in 9, you could write:
This finds serial numbers like ACB9, ACD9, or ACX9. It doesn’t return AC19 or AC99, because those have more than one character in the third position.
If you need to match several unknown characters, you can repeat the underscores. Here’s a case where the serial number must have three characters between AC and 9.
That will match something like ACXYZ9 or ACabc9, as long as there are exactly three characters before the 9. The difference between _ and % is that _ never adjusts. It expects one character per underscore. This is helpful when searching for structured data like codes or identifiers where the length and position of characters matter.
Escaping Wildcard Characters
Sometimes the stored text includes % or _. If the query treats those characters as wildcards, the results will be too broad. Add a backslash to tell the database to match the character itself.
Suppose you need every file named data%report.txt. The basic pattern does not work:
% is treated as a placeholder, so many extra rows appear.
Write the pattern with an escaped percent sign. The exact number of backslashes in the string literal depends on the engine.
MySQL needs two backslashes because a backslash escapes the next character inside its string literal. PostgreSQL keeps a single backslash when standard_conforming_strings is on, so only one is required there. SQL Server and Oracle accept a single backslash once the ESCAPE '\' clause is present.
The same rule applies to _. To locate the value user_001, write:
Without escaping, % and _ always behave as wildcards. Escaping provides exact text matching, which is vital when file names, identifiers, or user input can include these symbols.
Building Practical Search Queries with LIKE
Pattern-based searches show up all over the place, from filtering usernames and tags to looking through product descriptions or filenames. The LIKE operator gives you flexibility, but how you build the pattern affects both accuracy and speed. Different patterns have different costs. A search that starts with fixed text behaves very differently from one that looks inside the middle of a string. Each scenario calls for a slightly different shape in your query. Getting those details right is what keeps searches responsive and accurate at the same time.
Matching Beginning, Middle, Or End
When you know how a string starts, the pattern can be very efficient. Let’s say you’re searching for customers whose last names begin with “Will”. You’d write:
This matches Williams, Willoughby, and anything else that starts the same way. The reason this is fast is that databases often build indexes that follow the order of characters from left to right. Starting with a known prefix gives the engine a fixed starting point and helps it skip ahead instead of checking everything.
Ending matches work the other way. When your pattern starts with %, like this:
The engine has to scan each row and check the end of the string. There’s no anchor point at the beginning, so indexing doesn’t help here. That makes the search slower, especially as the number of rows grows.
Searching through the middle of the text is the most expensive. If you want to find product descriptions that mention a certain word, the query looks like this:
This checks every description and looks for a match anywhere in the string. It matches wireless keyboard, compact and wireless, and even ultrawirelesscombo. But the cost is high. The engine has to scan through each value from start to finish, comparing one character at a time. On small datasets, it’s not a big deal. On larger ones, it can slow things down.
Case Insensitive Matching
One of the tricky parts of text search is that case sensitivity depends on the database. In MySQL, for example, the collation on the column controls whether LIKE treats Wireless and wireless as the same. In PostgreSQL, you'd use ILIKE for a case-insensitive match. To keep things consistent across databases, you can convert both the input and the column to lowercase.
This works regardless of how the text is stored, but there’s a tradeoff. Calling a function on the column can stop the database from using indexes. That means the whole column has to be scanned every time.
A better option, if you have control over the schema, is to store the data with a case-insensitive collation. In MySQL, that would look like this:
With this in place, the query:
Will find Alex, alex, ALEX, and anything else that matches, all without needing to transform either side during the query.
Avoiding Slow Full Table Scans
Searches that begin with % can’t take advantage of indexes in most databases. When there’s no fixed starting point, the engine checks every row. That’s fine for a few thousand records, but as your table grows, this can slow things down.
One way to make the query less expensive is to combine the wildcard pattern with a filter that can use an index. Say you’re looking for users who signed up this year and used the word “developer” in their bio.
Here, the date filter cuts down the number of rows before the wildcard search begins. That helps limit how much work the engine has to do.
Another approach is to use full-text search when it’s available. Many modern databases support it. MySQL, for instance, lets you create a full-text index like this:
Then you can write a query like:
That kind of search is built for speed and handles partial matches, relevance, and large volumes of text better than LIKE.
Basic LIKE patterns are easy to use and good for simple searches, but it’s worth knowing when they start to hit performance limits. Combining them with other filters or switching to full-text search where needed helps keep things responsive as the data grows.
Conclusion
The LIKE operator works by comparing patterns one character at a time. % matches any length of text, and _ matches one single character. Each time a query runs, the engine checks how the pattern lines up with values in the column. Searches that start with fixed text are faster because the engine can skip ahead using an index. Patterns that begin with % slow things down because there’s no starting anchor. Case handling depends on collation or how the values are written. Escaping lets you search for actual wildcard characters without confusing the engine. Everything comes back to how the pattern is structured and how the engine reads it.