LIKE Predicate

The LIKE predicate performs pattern-matching queries.

Syntax

Column_Reference [NOT] LIKE 'String_Pattern '

Elements

Column_Reference

Specifies the column name (alias). Its data type must be compatible with the format of the String_Pattern 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:

Name	Symbol	Description
Percent	%	Matches 0 or more occurrences of the search pattern.
Underscore	_	Matches any single character.
Square brackets	[ ]	Matches any single character within a specified range, inside the square brackets.
Caret	[^]	Matches any single character not within the specified range, inside the square brackets.

Note: To use the percent ( % ), underscore ( _ ),and left square bracket ( [ ) as literal characters in a LIKE search pattern rather than as wildcards, 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 illustrates using wildcards as literals:

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]%'
```