The conditional operators LIKE and = (equals) can be used to construct expressions in which the condition involves matching a value to a pattern rather than to another explicit value.
The patterns are usually quoted character strings constructed using special wildcard characters in addition to literal characters.
Patterns for use with the LIKE operator can use the following wildcard characters:
- the underscore (_) matches any single character
- the percent sign (%) matches zero or any number of characters
For example, the pattern
matches any character string whose second and third letters are ob, followed by zero or more characters. Robert, Bob, and Robin (among others) would match this pattern. Therefore, the expression
FirstName like ‘_ ob%’
is logically true if the value of FirstName is Robert, Bob, Robin, or any other string that matches the given pattern.
If you need to use a percent sign or underscore as a literal character in the pattern, you must “escape” the wildcard character by inserting a backslash ( ) preceding the wildcard. You must also escape the backslash (two backslashes in total) as shown in the following example:
Grad like ‘100\%’
Very complex expressions can be used as patterns in a LIKE comparison. For example, if SuffixVar is a variable whose value is %, then the expression
FirstName like $ concat(‘S_e%p, SuffixVar)
is logically true if FirstName matches the pattern S_E%p%.
= (Equals) Patterns
Patterns for use with the = (equals) operator also support a wildcard character for pattern matching. The question mark (?) matches any number of characters at the end of a string. The following example
is logically true if DeptNum begins with the characters D56. Notice that the question mark must appear outside the pattern string.
If you wish to use the question mark as a literal character, it must be included in the pattern string (i.e., within the quotation marks). The following example
RemarkField = ‘Smith?’
is logically true only if the value of RemarkField is exactly Smith?.
The question mark wildcard can be appended to any atomic expression (i.e., literals, object names, and temporary names) provided that the expression has a character data type (i.e., ALPHA, CHAR, VARALPHA, and VARCHAR).
For example, suppose that LastName is a field in the fCustomer form, having the value Sm//. The expression
Surname = fCustomer.LastName?
is logically true if SurName begins with Sm. The trailing blanks in the pattern are ignored when the question mark is applied to a pattern that is not a literal.
If LastName has the value Sm?//, then the expression
Surname = fCustomer.Lastname
is logically true only if Surname is exactly Sm?.
Using the question mark wildcard in an = (equals) expression is equivalent to using a trailing percent sign wildcard in a LIKE expression. For example, the following expressions are equivalent:
FirstName = ‘ Sm’?
FirstName like ‘ Sm%’