JDBC escape syntax for LIKE clauses

The percent sign (%) and underscore (_) are metacharacters within SQL LIKE clauses. JDBC provides syntax to force these characters to be interpreted literally.

The JDBC clause immediately following a LIKE expression allows you to specify an escape character.

Syntax

WHERE characterExpression [ NOT ] LIKE
    characterExpressionWithWildCard
    { ESCAPE 'escapeCharacter' }

Examples

-- find all rows in which a begins with the character "%"
SELECT a FROM tabA WHERE a LIKE '$%%' {escape '$'}
-- find all rows in which a ends with the character "_"
SELECT a FROM tabA WHERE a LIKE '%=_' {escape '='}

Note: ? is not permitted as an escape character if the LIKE pattern is also a dynamic parameter (?).

In some languages, a single character consists of more than one collation unit (a 16-bit character). The escapeCharacter used in the escape clause must be a single collation unit in order to work properly.

You can also use the escape character sequence for LIKE without using JDBC's curly braces; see Boolean expressions.