Like Operator

The Like Operator can be used to compare two strings and provide pattern matching.
The behaviour of the Like operator depends on the Option Compare statement.
The default string comparison method is Option Compare Binary which means that string comparisons are based on a sort order derived from the binary representations of the characters meaning A < a.

If (sFirstName Like "a?b*" = True) Then 
End If

You can add "Option Compare Text" to the top of the module to force string comparisons to be based on case insensitive text. So "A" is the same as "a".
You can eliminate the case sensitivity when comparing strings by declaring Option Compare Text (ie A = a). Be aware that some fonts are not monospaced (eg Courier New), which means that characters are not equally spaced.
When comparing individual characters it is better to compare ASCII numbers using ASC(char) =- 137 rather than char = "%".

Pattern Characters [ result = String LIKE pattern ]

Pattern CharacterWhat it matches in a string
?Matches a single character
*Matches zero or more characters
#Matches a single digit (0-9)
[charlist]Matches a single character in charlist
[!charlist]Matches a single character not in the charlist

You can use a hyphen to separate the lower and upper bounds, eg [A-Z]. There are no delimiters.

Special Characters

To match the special characters, left bracket ([), question mark (?), number sign {#) and asterisk (*), enclose them in brackets.
The right bracket (]) cannot be used within a group to match itself, but it can be used outside a group as an individual character.
A quick way to return the character in a particular position in a string is to use the MID(sText,iCharNo,1)

Pattern Characters 
[ABC]"A" or "B" or "C"
[A - D]"A" or "B" or "C" or "D"
[A - Z]Any uppercase alphabetical character
[A - Za - z]Any uppercase or lowercase alphabetical character

"ABC" Like "A?C" = True 
"A4C Like "A#C" = True
"ABC" Like "A??C" = False
"A123C" Like "A*C" = True
"1B3" Like "1[A-Z]3" = True
"123" Like "1[!A-Z]3" = True
"0207 457 1234" Like "#### ### ####" = True

WIldcard Characters

In VBA this can be done by placing these characters inside square brackets [ ].

Debug.Print "a*c" Like "a[*]c"      '= True   
Debug.Print "a?c" Like "a[?]c" '= True
Debug.Print "abc" Like "a[*]c" '= False
Debug.Print "abc" Like "a[?]c" '= False
Debug.Print "abc" Like "a*c" '= True
Debug.Print "abc" Like "a?c" '= True

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext