Pattern Matching Using the LIKE Operator v11
Advanced Server provides pattern matching using the traditional SQL LIKE
operator. The syntax for the LIKE
operator is as follows.
Every pattern
defines a set of strings. The LIKE
expression returns TRUE
if string
is contained in the set of strings represented by pattern
. As expected, the NOT LIKE
expression returns FALSE
if LIKE
returns TRUE
, and vice versa. An equivalent expression is NOT (string LIKE pattern)
.
If pattern
does not contain percent signs or underscore, then the pattern only represents the string itself; in that case LIKE
acts like the equals operator. An underscore (_
) in pattern
stands for (matches) any single character; a percent sign (%
) matches any string of zero or more characters.
Some examples:
LIKE
pattern matches always cover the entire string. To match a pattern anywhere within a string, the pattern must therefore start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern
must be preceded by the escape character. The default escape character is the backslash but a different one may be selected by using the ESCAPE
clause. To match the escape character itself, write two escape characters.
Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement. Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE
; then a backslash is not special to LIKE
anymore. (But it is still special to the string literal parser, so you still need two of them.)
It’s also possible to select no escape character by writing ESCAPE ''
. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern.