In this post, we will be demonstrating how you can use the LIKE operator in queries.
Being able to filter queries is an important part of effective data retrieval. Although we often want to find exact matching values, sometimes we wish to search for a pattern within a string or a string which ends a certain way. For this purpose, we have the LIKE operator!
We are going to use the Northwind database to demonstrate how the LIKE operator works.
We are going to be filtering data in the Products table. The Products table (unfiltered) looks like this:
The LIKE Operator
Create a new query, add in the Products table and replicate the field arrangement we have below. In the criteria field of Product Name write Like “*Ch*”:
We are asking the query to return all records where the field Product Name contains the string Ch. The result is below:
In the results above, the Product Name doesn’t just start with the string Ch, they contain the string Ch. How exactly do we do that?
Filtering Patterns
When we filter with LIKE, we are asking the query to return results that match a particular pattern. There are two important characters that we need to know about:
* (The Asterisk)
? (The Question Mark)
The Asterisk means “show me any number of any unknown characters”.
The Question Mark means “show me one of any unknown characters”.
Some examples might help.
Using a single Question Mark in the Product Name field
| Criteria | Results |
|---|---|
Like “NWTS-6?”We are looking for a string “NWTS-6” PLUS one extra character |
NWTS-65, NWTS-66 |
Using Multiple Question Marks in the Product Name field
NWTS-8
| Criteria | Results |
|---|---|
Like “??????”We are looking for any string that is 6 characters in length. |
NWTB-1, NWTO-5 |
| Criteria | Results |
|---|---|
Like “N?T?-?”We are looking for any string that where the 1st letter is “N”, the 2nd is any character, the 3rd is “T”, the 4th is any character, the 5th is “-” and the 6th is any character. |
NWTB-1, NWTO-5, NWTS-8 |
Using Asterisks
| Criteria | Results |
|---|---|
Like “NWTP*”We are looking for a string “NWTP” PLUS any number of unknown characters. |
NWTP-56, NWTP-57 |
| Criteria | Results |
|---|---|
Like “*89”We are looking for any strings that end with “89” |
NWTCFV-89 |
Combining Asterisks and Question Marks
| Criteria | Results |
|---|---|
Like “*TB???”We are looking for a Product Name that contains the string “TB” and has 3 unknown characters at the end. |
NWTB-34, NWTB-43, NWTB-81, NWTB-87 |
| Criteria | Results |
|---|---|
Like “N?T*4”We are looking for a string that begins with the letter “N”, has a single unknown character for the 2nd letter, a “T” for the third letter, any number of unknown characters after that and ends with a “4”. |
NWTB-34, NWTCFV-94, NWTCO-4, NWTDFN-14, NWTDFN-74 |
It is well worth the effort to master using the LIKE operator as it will enable you to generate much more fluid queries in Access.
Related Posts
Artithmetic Operators OperatorsLogical Operators Operators
The Query Designer Explained Queries
Thanks for the tutorial, Robert.
Is there any way (maybe programmatically) to force MS Access to accept % as the wildcard in place of (or in addition to) * ?
I’m irritated that I can’t remember that the wildcard is different for MS Access, and I almost always have to look up the syntax when my queries won’t run with % 🙂 Thnx.
I have the same Problem :(. did u find any solution for it ?
Hi Gray
I haven’t been asked this before but did some digging and it looks like you would need to change the Query mode.
You would to change it from ANSI-89 to ANSI-92 (https://support.microsoft.com/en-us/office/comparison-of-microsoft-access-sql-and-ansi-sql-93edf744-9070-4af8-bcf9-1953a87bd6e5).
Here is a post for doing that with 2007 https://btabdevelopment.com/how-to-set-the-ansi-92-option/.
Whichever version of MS Access, you have, as long as you can find the object designers, you will be able to change the query type.
Regards
excelent guide, until now I undertand the operator like.
greetings from mexico city
Buenos dias, amigo