Like Operator
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 | Using Wildcard Characters in String Comparisons |
| WHERE Clause (Microsoft Jet SQL) |