Using The Like Operator In Queries

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:

Products TableThe 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*”:

Basic Query Designer We are asking the query to return all records where the field Product Name contains the string Ch. The result is below:

Simple QueryIn 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
Logical Operators
The Query Designer Explained

5 Comments

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube