MS-Access / Getting Started

---

LIKE Predicate

Searches for strings that match a pattern.

Syntax

column-name [NOT] LIKE match-string [ESCAPE escape-character]

Notes: String comparisons in Access or a default installation of SQL Server are case-insensitive. If the column specified by column-name contains a Null, the result is undefined. Comparison of two empty strings or an empty string with the special asterisk (*) character (% character in SQL Server) evaluates to True.

You provide a text string as a match-string value that defines what characters can exist in which positions for the comparison to be true. Access and SQL Server understand a number of wildcard characters (shown in Table-2) that you can use to define positions that can contain any single character, zero or more characters, or any single number.

Table-2 Wildcard Characters for String Comparisons

Desktop Database	Project File	Meaning
?	-	Any single character
*	%	Zero or more characters (used to define leading, trailing, or embedded strings that don't have to match any of the pattern characters)
#	[0-9]	Any single number

You can also specify in the match string that any particular position in the text or memo field can contain only characters from a list that you provide. To define a list of comparison characters for a particular position, enclose the list in brackets ([ ]). You can specify a range of characters within a list by entering the low-value character, a hyphen, and the high-value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation point (!) in a desktop database or a caret symbol (^) in a project file.

If you want to test for one of the special characters *, ?, #, and [, (and _ or % in a project file), you must enclose the character in brackets. Alternatively, in a project file, you can specify an ESCAPE clause. When you place the escape character in the match string, the database ignores the character and uses the following character as a literal comparison value. Therefore, you can include the escape character immediately preceding one of the special characters to use the special character as a literal comparison instead of a pattern character. Desktop databases do not support the ESCAPE clause.

Examples

In a desktop database, to determine whether a contact's LastName is at least four characters long and begins with Smi, enter the following:

tblContacts.LastName LIKE "Smi?*"

In a project file, write the previous test as follows:

tblContacts.LastName LIKE 'Smi_%'

In a desktop database, to test whether PostalCode is a valid Canadian postal code, enter the following:

PostalCode LIKE "[A-Z]#[A-Z] #[A-Z]#"

In a project file, to test whether a character column named Discount ends in 5%, enter the following:

Discount LIKE '%5$%' ESCAPE '$'