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, Adaptive Server generates an exception.

Do not use existing wildcard characters as escape characters because:

• If you specify the underscore ( _ ) or percent sign (%) as an escape character, it loses its special meaning within that like predicate and acts only as an escape character.

• If you specify the left or right bracket ( [ or ] ) as an escape character, the Transact-SQL meaning of the bracket is disabled within that like predicate.

• If you specify the hyphen (-) or caret (^) as an escape character, it loses its special meaning and acts only as an escape character.

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.