Using the Like operator and wildcard characters in string comparisons

Applies To
Access for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Built-in pattern matching helps you compare strings. The following table shows the wildcard characters that you can use with the Like operator and what they match.

Character(s) in pattern Matches in expression
? or _ (underscore) Any single character
* or % Zero or more characters
# Any single digit (0-9)
[*charlist*] Any single character in charlist
[!*charlist*] Any single character not in charlist

You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in expression. Charlist can include almost any characters in the ANSI character set, including digits. You can use the special characters opening bracket ([), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if they're enclosed in brackets. You can't use the closing bracket (]) within a group to match itself, but you can use it outside a group as an individual character.

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] in pattern results in a match if the corresponding character position in expression contains any uppercase letter in the range A through Z. You can include multiple ranges within the brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.

The ANSI SQL wildcards (% and _) are available only with the Microsoft Access database engine and the Access OLE DB Provider. Access or DAO treats them as literals.

Other important rules for pattern matching include the following:

  • An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist is found in expression. When used outside brackets, the exclamation mark matches itself.
  • You can use the hyphen (-) either at the beginning of charlist (after an exclamation mark, if one is used) or at the end of charlist to match itself. In any other location, the hyphen identifies a range of ANSI characters.
  • When you specify a range of characters, the characters must appear in ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] is not.
  • The character sequence [ ] is ignored. It's considered a zero-length string ("").