Pattern-matching Predicates
Pattern-matching predicates (the LIKE family of predicates) are used to search for a specified pattern in text, an expression, or column. These predicates include:
• LIKE
• BEGINNING, ENDING, CONTAINING
• SIMILAR TO
The specific predicate to use depends on how complex the pattern needs to be. All predicates in the LIKE family have the same SQL syntax, operate on the same data types, and can control the case-sensitivity of the matching.
The LIKE family of predicates performs pattern matching for the character data types (c, char, varchar, long varchar, and text) and Unicode data types (nchar, nvarchar, and long nvarchar).
The LIKE family of predicates has the following syntax:
expression [NOT] [LIKE|BEGINNING|CONTAINING|ENDING|SIMILAR TO] pattern
[WITH CASE | WITHOUT CASE]
[ESCAPE escape_character]
where
expression
Is a column name or a string expression
pattern
Specifies the pattern to be matched. The pattern is typically a string literal but can be an arbitrary string expression.
The patterns supported depends upon the specific predicate used.
WITH CASE | WITHOUT CASE
Indicates whether to match the case of the pattern. This option can be used before, after, or instead of the ESCAPE clause.
Default: If not specified, the collation type of the expression is used, typically WITH CASE.
ESCAPE escape_character
Specifies the character to use to escape another character or to enable a character's special meaning in a pattern, such as to enable a LIKE set pattern with '\[a-z\]' where '\' is the escape character.