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:


WHERE CharacterExpression [ NOT ] LIKE
    { ESCAPE 'escapeCharacter' }

-- 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 expression.

Related reference
JDBC escape keyword for call statements
JDBC escape syntax
JDBC escape syntax for fn keyword
JDBC escape syntax for outer joins
JDBC espace syntax for time formats
JDBC escape syntax for timestamp formats