Like Operator

Description

Used to compare a string expression to a pattern in an SQL expression.

Syntax

Expression Like "pattern"

The Like operator syntax has these parts.

Part	Description

expression	SQL expression used in a WHERE clause.
pattern	String or character string literal against which expression is compared.

Remarks

You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example, Like "Smith"), or you can use wildcard characters like those recognized by the operating system to find a range of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you enter Like "C*" in an SQL query, the query returns all field values beginning with the letter C. In a parameter query, you can prompt the user for a pattern to search for.

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]###"

See Also

SQL Expressions.

Specifics (Microsoft Access)

In Microsoft Access, you can use the Like operator in a query expression, in a macro or module, or in a calculated control on a form or report.

The case sensitivity and character sort order of the Like operator depend on the setting of the New Database Sort Order box on the General tab of the Options dialog box, available by clicking Options on the Tools menu. By default, the New Database Sort Order is set to General, which specifies a case-insensitive sort order for the database.

You can alter the sort order for an individual Visual Basic module by changing the Option Compare statement in the Declarations section of a module. By default the Declarations section is set to Option Compare Database, which specifies the same sort order that currently applies to the rest of the database. If the module doesn't contain an Option Compare statement, the default string-comparison method is Binary, which is case-sensitive.

You can use the Like operator to specify inexact criteria in the query design grid. For example, if you type Like "C*" in the Criteria row of the query design grid, the query returns all field values beginning with the letter C.

In a parameter query, you can use the Like operator to prompt the user for a pattern to search for. For example, suppose you have an Employees table that includes a LastName field. In the Query window, create a new query by adding the Employees table and dragging the LastName field to the grid. Enter the following expression in the Criteria row.

Like [Enter first few letters of name:]&"*"

When the query is run, a dialog box prompts the user with "Enter first few letters of name:". If the user types Sm in the dialog box, the query looks for the pattern Sm* — that is, all names beginning with the letters Sm.

You can use Like in an expression as a setting for the ValidationRule property or as a macro condition. For example, you can restrict data entered in a text box control to an inexact specification. In the ValidationRule property of the text box, enter the following expression.

Like "P[A-F]###"

Data entered in this text box must now begin with the letter P, followed by any letter between A and F and three digits.

Example

This example returns a list of employees whose names begin with the letters A through D.

SELECT * FROM Employees WHERE LastName Like "[A-D]*"

The following table shows how you can use Like to test expressions for different patterns.

Kind of match	Pattern	Match (returns True)	No match (returns False)

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"