LIKE Predicate
The LIKE predicate performs queries using wildcard-character pattern matching. This predicate is an optional part of the optional WHERE clause of the SELECT statement.
SELECT Select_List | *
FROM_Clause
[WHERE Column_Reference [NOT] LIKE 'String_Pattern']
[ORDER_BY_Clause]
Parameters
- Select_List
- Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query.
- * (asterisk)
- Specifies all columns. This option is valid only when the FROM_Clause parameter references a predefined view or a temporary view.
- FROM_Clause
- Specifies the files on which to perform the search. For details about this parameter, see FROM Clause.
- Column_Reference
- Specifies the column name (alias). Its data type must be compatible with the format of the String_Pattern parameter specified.
- String_Pattern
- Specifies the literal of type Basic String to use as the pattern. You can use any combination of string literals along with the valid wildcard characters shown in the following table.
| Wildcard Character | Symbol | Description |
|---|
| Percent | % | Matches one or more characters. |
| Underscore | _ | Matches one character. |
| Square brackets | [ ] | Matches any single character in the range or set specified within the brackets. |
| Caret | ^ | Matches any single character not within the specified range or set. |
- ORDER_BY_Clause
- Specifies the ordering of the resulting rowset. This clause is optional. For details about this parameter, see ORDER BY Clause.
Remarks
In a LIKE search pattern, to use the percent sign ( % ), underscore ( _ ),and left square bracket ( [ ) as literal characters rather than as wildcard characters, surround the characters with square brackets. The right square bracket ( ] ) matches itself unless preceded by a left square bracket. The range character ( - ) matches itself unless it is inside square brackets and preceded and followed by a single character.
The following table shows patterns that illustrate using wildcard characters as literal characters.
| Pattern | Meaning |
|---|
| LIKE 'd%' | d followed by any string of 0 or more characters |
| LIKE 'd[%]' | d% |
| LIKE '_n' | an, in, on, etc. |
| LIKE '[_]n' | _n |
| LIKE '[a-cdf]' | a, b, c, d, or f |
| LIKE '[-acdf]' | -, a, c, d, or f |
| LIKE '[[]' | [ |
| LIKE ']' | ] |
| LIKE '[ab]cd]e' | acd]e, or bcd]e |
Examples
The following example returns rows consisting of the DocAuthor, DocTitle, and size properties for all files under the virtual roots "/contracts" and "/legal", written by authors whose names are "Smith", "Smyth", "Smythe", and so on, where the comment field of those documents does not contain words starting with "real", such as "realty" or "realtor".
SELECT DocAuthor, DocTitle, size
FROM SCOPE('"/contracts", "/legal"')
WHERE DocAuthor LIKE 'SM_TH%'
AND DocComments NOT LIKE 'REAL%'
The following example returns rows consisting of the DocTitle and size properties for all files under the virtual roots "/contracts" and "/legal", written by authors whose names begin with any characters except "A" through "F".
SELECT DocTitle, size
FROM SCOPE('"/contracts", "/legal"')
WHERE DocAuthor LIKE '[^a-f]%'
See Also
ARRAY Predicate, Comparison Predicate, CONTAINS Predicate, FREETEXT Predicate, MATCHES Predicate, NULL Predicate, WHERE Clause