The Like criteria or operator is used in an Access query to find data that matches a specific pattern. For example, in a sample database, we have a "Customers" table, like the one below, and we want to locate only the customers living in cities whose names start with "B". Here’s how we’ll create a query and use the Like criteria:
-
Open the Customers table:
-
On the Create tab, click Query Design.
-
Click Add and the Customers table gets added to the query designer.
-
Double-click the Last Name and City fields to add them to the query design grid.
-
In the City field, add the “Like B*” criteria, and click Run.
The query results show only the customers from cities names starting with the letter “B”.
To learn more about using criteria, see Apply criteria to a query.
Using the Like operator in SQL syntax
If you prefer doing this in SQL (Structured Query Language) syntax, here’s how:
-
Open the Customers table and on the Create tab, click Query Design.
-
On the Home tab, click View > SQL View and type the following syntax:
SELECT [Last Name], City FROM Customers WHERE City Like “B*”;
-
Click Run.
-
Right-click the query tab, Save > Close.
For more information see Access SQL: basic concepts, vocabulary, and syntax and learn more about how to edit SQL statements to sharpen query results.
Examples of Like criteria patterns and results
The Like criteria or operator comes in handy while comparing a field value to a string expression. The following example returns data that begins with the letter P followed by any letter between A and F and three digits:
Like “P[A-F]###”
Here are some ways of using Like for different patterns:
|
|
|
If your database has a match |
If your database doesn’t have a match |
|---|---|---|---|
|
Multiple characters |
a*a |
aa, aBa, aBBBa |
aBC |
|
*ab* |
abc, AABB, Xab |
aZb, bac |
|
|
Special character |
a[*]a |
a*a |
Aaa |
|
Multiple characters |
ab* |
abcdefg, abc |
cab, aab |
|
Single character |
a?a |
aaa, a3a, aBa |
aBBBa |
|
Single digit |
a#a |
a0a, a1a, a2a |
aaa, a10a |
|
Range of characters |
[a-z] |
f, p, j |
2, & |
|
Outside a range |
[!a-z] |
9, &, % |
b, a |
|
Not a digit |
[!0-9] |
A, a, &, ~ |
0, 1, 9 |
|
Combined |
a[!b-m]# |
An9, az0, a99 |
abc, aj0 |
Examples of Like criteria with wildcard characters
The following table shows types of results when the Like criterion is used with a wildcard characters in a table that might contain data with the specific matching patterns.
|
Criteria |
Result |
|---|---|
|
Like “E#” |
Returns items with just two characters where the first character is E and the second is a number. |
|
Like “G?” |
Returns items with only two characters where the first character is G. |
|
Like “*16” |
Returns items ending on 16. |
See more examples of wildcard characters.
