Using the escape Clause

Use the escape clause to specify an escape character. Any single character in the server’s default character set can be used as an escape character. If you try to use more than one character as an escape character, the SAP ASE server generates an exception.

Do not use existing wildcard characters as escape characters because:

An escape character retains its special meaning within square brackets, unlike wildcard characters such as the underscore, the percent sign, and the open bracket.

The escape character is valid only within its like predicate and has no effect on other like predicates contained in the same statement. The only characters that are valid following an escape character are the wildcard characters ( _, %, [, ], or [^] ), and the escape character itself. The escape character affects only the character following it, and subsequent characters are not affected by it.

If the pattern contains two literal occurrences of the character that happens to be the escape character, the string must contain four consecutive escape characters. If the escape character does not divide the pattern into pieces of one or two characters, the SAP ASE server returns an error message. Examples of escape clauses used with like are:

Using the Escape Clause

like predicate

Meaning

like "5@%" escape "@"

5%

like "*_n" escape "*"

_n

like "%80@%%" escape "@"

String containing 80%

like "*_sql**%" escape "*"

String containing _sql*

like "%#####_#%%" escape "#"

String containing ##_%