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
Use the Like operator to find values in a field that match the pattern you specify. For pattern, specify the complete value (for example, Like "Smith"
), or use wildcard characters to find a range of values (for example, Like "Sm*")
.
In an expression, 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 |