JDBC escape syntax for LIKE clauses
Derby Reference Manual
324
Example
-- Call a Java procedure
{ call TOURS.BOOK_TOUR(?, ?) }
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
JDBC escape syntax for fn keyword
You can specify functions in JDBC escape syntax by using the
fn
keyword.
Syntax
{fn functionCall}
where functionCall is the name of one of the scalar functions listed below. The functions
are of the following types:
· Numeric functions
· String functions
· Date and time functions
· System function
Numeric functions
abs
Returns the absolute value of a number.
abs(NumericExpression)
The JDBC escape syntax
{fn abs(NumericExpression)}
is equivalent to the
built-in syntax
ABS(NumericExpression)
. For more information, see
.
acos
