When you use the LIKE operator in an And Where expression, you can use any of the following metacharacters:
. The dot metacharacter matches a single instance of any single character.
For example: "bat." is a pattern that matches any four character string that begins with "bat". It would retrieve "bata", ‘batb", but not "batab" (because it is longer than four characters, the length of "bat.").
As another example: "basebal." would match any eight character string that begins with "basebal".
"basebal.xyz" would match any character string that begins with "basebal", then has any single character, then ends with "xyz".
The dot metacharacter can also return a space: so "basebal.xyz" can also return "basebal xyz (where a space is between the "l" and the "x".
* The star metacharacter matches zero or more of the preceding character in the expression. An expression followed by an asterisk "*" can be repeated any number of times (including zero, (i.e., 0-n times)).
For example: "ba*" matches all of "b", "ba", "baaa", "be", "beee", etc., but not "aaaa" because it doesn’t start with a "b"
The star metacharacter can also return spaces: so "b *" (a space between the "b" and the * metacharacter) can return "b ", and "b ".
^ The NOT metacharacter matches patterns by using the characters to the left of the ^ metacharacter and excluding those to the right of the ^ metacharacter.
For example: "bat^abc" is a pattern that matches any string (regardless of size) that begins with "bat" (the characters to the left of the ^ metacharacter) and isn’t followed by abc (the characters to the right of the ^ metacharacter).
Using the pattern "bat^abc", "batabc" would not be returned while "batabc123" would be returned. The first would not be returned because it ends with "abc" (the pattern to the right of the ^ metacharacter, while the second would be returned because begins with "bat" (the characters defined to the left of the ^ metacharacter) and it ends with "abc123" which is NOT the pattern to the right of the ^ metacharacter.
"^bat" means match anything except "bat", while "^xyz" means match anything except "xyz".
The ^ metacharacter can block out spaces as well: so "^this is a test" will return "thisisatest", but will not return "this is a test".
$ The dollar metacharacter matches expressions only at the end of the string
For example: "bag$" will return "airbag", but not "baguette".
\ The backslash metacharacter is used as an ESCAPE code so that ProjectConsole metacharacters (those listed here) can be used as literals.
For example, "\\" means that you want a literal backslash, while \$ means that you want a literal dollar-sign.
As a further example, if you are specifying a path that includes backslashes ("\"), you must use double backslashes as follows: Path LIKE ‘C:\xyz’ returns nothing, while Path LIKE ‘C:\\xyz’ returns all targets items in the directory C:\xyz
DO NOT escape Alphanumeric characters (i.e., "a-z", "A-Z", or "0-9"), if you do, the results may be unpredictable.
"The backslash metacharacter MUST also be used when using plus-signs (+). For example, when looking for a particular style in a Word document, you may need to use the plus-sign (+) to identify style definitions. While the following would be incorrect: "ading 3 + Font: 10 pt, Indent: Hanging", the following would be correct: "ading 3 \+ Font: 10 pt, Indent: Hanging""
[ ] The bracket metacharacters match any one of the enclosed characters. Characters between the brackets are to be matched.
For example: "bat[123]xyz" would match "bat1xyz", and "bat2xyz", and "bat3xyz", but NOT "bat123xyz" (because the bracket metacharacter applies to single character matches only).
To match "bat123xyz" and "bat321xyz" and "bat312xyz" would require the following pattern to be used: "bat[123][123][123]xyz".
A dash may be used with the bracket metacharacter. For example: "Name LIKE bat[a d] would return "bata", "batb", "batc", and "batd".
An interesting pattern for the bracket metacharacter is the inclusion of the * metacharacter. For example, "bat[xyz*] means that "batx" is returned, "baty" is returned, "batz" is returned, and "batzzzzzz" is returned, but NOT "batzzzz" or "batyyyy".
Another interesting pattern for the bracket metacharacter is the inclusion of the NOT metacharacter (^). For example, you can use the pattern "bat[^123]xyz" to obtain any string that begins with "bat", then has a single character that is not "1" or "2" or "3", and ends with "xyz". Such a pattern would return "batHxyz", but would not return "bat1xyz".
The backslash metacharacter (\) can be used with the backslash metacharacter. For example: "bat[\\abc]xyz" returns any character string that begins with "bat", then has a single backslash (remember, the double backslash means that you are ESCAPing a single backslash), or an "a", or a "b", or a "c", finally ending with "xyz".
The bracket metacharacter pair can also contain spaces, meaning that a returned string can have an embedded space.
Final Exam on Metacharacters:
What values might the following metacharacter combination return: "base*..[\\x][^7]*$ball" ?
Answer:
Well, let’s look at it. First of all, note that there are a number of metacharacters being used: "*", ".", "[ ]", "\", and "$". Any returned string will begin with "bas", the characters to the left of the first metacharacter (not including the character to the "immediate" left of the first metacharacter). Then the first * metacharacter will return any number of "e"s (zero to infinity, well not quite infinite, more like a very large number of them). So up to this point, the following would qualify: "bas", "base", and "baseeeeeeee". Next we have a pair of dot metacharacters. These characters return any single character, and being that there are two of this type of metacharacter, we get two single characters of any kind. So now we can get "base56", "baseeeeHX", or even "bas \" (note: the backslash is a returned value and has nothing to do with the "\" metacharacter) (also note the space between the "s" and the "\", as dot metacharacters can return spaces).
Continuing, we come upon the first pair of bracket metacharacters "[\\x]". This pair returns a match if a backslash or the letter "x" exist at this position. So now we can get "base56\", "baseeeeeeeeeZHx", or even "baseee \\" (Here the double backslash is not to be confused with the ESCAPE metacharacter. The first of the pair comes form the second dot metacharacter (as discussed above, and the second backslash comes from the ESCAPEd pair defined by the bracket metacharacter.
Following the first bracket metacharacter combination is a second pair of bracket metacharacters: "[^7]". This pair states anything can be included at this point in the string except a 7. Now our string can consist of such things as: "baseeee56xY", or even "baseeeeee23xH, but NOT "baseee56x7".
Are you catching on? Next we find another * metacharacter. What is interesting about this particular instance of the * metacharacter is that it is acting against a bracket metacharacter that contains a ^ metacharacter. So what does it mean? It means that we are multiplying (repeating, if you will), the ^ metacharacter. So, you can get such things as "baseee56xY", or even "baseeee56xYYYYYY", but not "baseee56x7", or "baseeeee56x77777" (as the * metacharacter is acting on the "[^7]" combination.
Next we come to the final metacharacter combination: "$ball". This one means that any string that complies with the discussion above can be used, AND those strings can end with anything except the string "ball". Thus "baseeeee56xyballlll" would be returned by ProjectConsole, but "baseeeee56xyball" would not.