Like Operator

Description

Used to compare two strings.

Syntax

result = string Like pattern

The Like operator syntax has these parts:

Part	Description

result	Required; any numeric variable.
string	Required; any string expression.
pattern	Required; any string expression conforming to the pattern-matching conventions described in Remarks.

Remarks

If string matches pattern, result is True; if there is no match, result is False. If either string or pattern is Null, result is Null.

The behavior of the Like operator depends on the Option Compare statement. The default string-comparison method for each module is Option Compare Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. In the following example, a typical binary sort order is shown:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Option Compare Text results in string comparisons based on a case-insensitive, textual sort order determined by your system's locale. When you sort The same characters using Option Compare Text, the following text sort order is produced:

(A=a) < (À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Built-in pattern matching provides a versatile tool for string comparisons. The pattern-matching features allow you to use wildcard characters, character lists, or character ranges, in any combination, to match strings. The following table shows the characters allowed in pattern and what they match:

Characters in pattern	Matches in string

?	Any single character.
*	Zero or more characters.
#	Any single digit (0 – 9).
[charlist]	Any single character in charlist.
[!charlist]	Any single character not in charlist.

A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in string and can include almost any character code, including digits.

Note To match the special characters left bracket ([), question mark (?), number sign (#), and asterisk (*), enclose them in brackets. The right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

By using a hyphen (-) to separate the upper and lower bounds of the range, charlist can specify a range of characters. For example, [A-Z] results in a match if the corresponding character position in string contains any uppercase letters in the range A – Z. Multiple ranges are included within the brackets without delimiters.

The meaning of a specified range depends on the character ordering valid at run time (as determined by Option Compare and the locale setting of the system the code is running on). Using the Option Compare Binary example, the range [A–E] matches A, B and E. With Option Compare Text, [A–E] matches A, a, À, à, B, b, E, e. The range does not match Ê or ê because accented characters fall after unaccented characters in the sort order.

Other important rules for pattern matching include the following:

An exclamation point (!) at the beginning of charlist means that a match is made if any character except the characters in charlist is found in string. When used outside brackets, the exclamation point matches itself.
A hyphen (-) can appear either at the beginning (after an exclamation point if one is used) or at the end of charlist to match itself. In any other location, the hyphen is used to identify a range of characters.
When a range of characters is specified, they must appear in ascending sort order (from lowest to highest). [A-Z] is a valid pattern, but [Z-A] is not.
The character sequence [] is considered a zero-length string (" ").

In some languages, there are special characters in the alphabet that represent two separate characters. For example, several languages use the character "æ" to represent the characters "a" and "e" when they appear together. The Like operator recognizes that the single special character and the two individual characters are equivalent.

When a language that uses a special character is specified in the system locale settings, an occurrence of the single special character in either pattern or string matches the equivalent 2-character sequence in the other string. Similarly, a single special character in pattern enclosed in brackets (by itself, in a list, or in a range) matches the equivalent 2-character sequence in string.

See Also

Comparison operators, InStr function, Operator precedence, Option Compare statement, StrComp function.

Specifics (Macintosh)

On the Macintosh, sort order is determined by the character set.

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 uses the Like operator to compare a string to a pattern.

Dim MyCheck
MyCheck = "aBBBa" Like "a*a"            ' Returns True.
MyCheck = "F" Like "[A-Z]"                ' Returns True.
MyCheck = "F" Like "[!A-Z]"                ' Returns False.
MyCheck = "a2a" Like "a#a"                ' Returns True.
MyCheck = "aM5b" Like "a[L-P]#[!c-e]"        ' Returns True.
MyCheck = "BAT123khg" Like "B?T*"        ' Returns True.
MyCheck = "CAT123khg" Like "B?T*"        ' Returns False.

Example (Microsoft Excel)

This example deletes every defined name that contains "temp". The Option Compare Text statement must be included at the top of any module that contains this example.

For Each nm In ActiveWorkbook.Names
    If nm.Name Like "*temp*" Then
        nm.Delete
    End If
Next nm

This example adds an arrowhead to every shape on Sheet1 that has the word "Line" in its name.

For Each d In Worksheets("Sheet1").DrawingObjects
    If d.Name Like "*Line*" Then
        d.ArrowHeadLength = xlLong
        d.ArrowHeadStyle = xlOpen
        d.ArrowHeadWidth = xlNarrow
    End If
Next