Like Operator (Microsoft Access SQL)

Compares a string expression to a pattern in an SQL expression.

Syntax

expression Like "pattern"

The Like operator syntax has these parts:

PartDescription
expressionSQL expression used in a WHERE clause.
patternString 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 PatternMatch (returns True)No match (returns False)
Multiple charactersa*aaa, aBa, aBBBaaBC
*ab*abc, AABB, XabaZb, bac
Special charactera[*]aa*aaaa
Multiple charactersab*abcdefg, abccab, aab
Single charactera?aaaa, a3a, aBaaBBBa
Single digita#aa0a, a1a, a2aaaa, a10a
Range of characters[a-z]f, p, j2, &
Outside a range[!a-z]9, &, %b, a
Not a digit[!0-9]A, a, &, ~0, 1, 9
Combineda[!b-m]#An9, az0, a99abc, aj0

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
See Also

SQL Expressions

Using Wildcard Characters in String Comparisons

WHERE Clause (Microsoft Access SQL)



Community Additions

Show: