Like Operator

Description

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

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

See Also SQL Expressions, WHERE clause.

Specifics (Microsoft Access)

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

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 cell:

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 the Like operator in an expression as a setting for the ValidationRule property. 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.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub LikeX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Return a list of employees whose names begin with
    ' the letters A through D.
    Set rst = dbs.OpenRecordset("SELECT LastName," _
        & " FirstName FROM Employees" _
        & " WHERE LastName Like '[A-D]*';")

    ' Populate the Recordset.
    rst.MoveLast

    ' Call EnumFields to print the contents of the
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 15
    
    dbs.Close

End Sub

Example (Microsoft Access)

The following example returns a list of employees whose names begin with the letters A through D. To try this example in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste the example into the SQL window, and run the query.

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