Survey Tabulation > Advanced expressions > Operators > Categorical comparison operators > Like operator
 
Like operator
Like is a special comparison operator that can be used to compare two text values. You can use wildcards on the right side of the operator to define a pattern to be matched. The comparison is case sensitive. The following table shows the wildcards that you can use.
Wildcard
Description
%
Matches any sequence of zero or more characters.
_
Matches any single character.
If you want to match one of the wildcard characters, precede the character with a backslash (\).
Examples
These examples are based on the Museum sample data set and show how you would use the LIKE operator in mrScriptBasic or mrScriptMetadata. In SQL queries, you should enclose text strings in single quotation marks (' ') instead of double quotation marks (" ").
address LIKE "%London%"
address is a text variable that records respondents' addresses. The Like operator in this example compares whether the value in the variable contains the text "London" in any position. For example, you could use this expression to select respondents who have the word "London" anywhere in their address. However, this expression would include respondents who live, for example, in London House in New York and would exclude respondents who have London spelled with any other combination of upper and lower case characters (such as LONDON or london) or misspelled (such as Londoon).
address LIKE "%London"
Notice that the percent sign has been removed from the end of the pattern. The expression now selects respondents only if the word "London" is at the end of their address. In fact there are no respondents who have the word "London" at the end of their address in the Museum sample, because all of the London addresses end with the area code.
address LIKE "%London_S%"
Now the pattern has been changed to include an underscore wildcard followed by the letter "S" and the percent wildcard. The underscore wildcard matches any single character, so this expression selects respondents who have the word "London" followed by an upper case "S" with any one character in between. For example, this expression would select respondents with the following addresses:
151 Lineacre Road, London SE2
4 Alexandra Road South, London SE19
1 Heathcode Ave, London SW16
However, it would not select respondents with the following addresses:
24 Heather Close, London, SE10
Loganberry Hall, Loganberry Road, London sw1
The first of these is excluded because there are two characters between the word "London" and the character "S". The second is excluded because the "s" is not uppercase.
* In UNICOM Intelligence Data Model 2.7 and earlier, LIKE generally searches case insensitively in a relational MR database when SQL Server has been installed with the case-insensitive collation option. However, the search is case sensitive when you use the new RDB DSC 2 or when the LIKE keyword is included in a complex expression (for example, one that includes a function from the UNICOM Intelligence Function Library) it is evaluated case sensitively. For further information, see What's new in the Relational MR database CDSC.
See also
Categorical comparison operators