Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
LIKE predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
LIKE
predicates are popular for simple wildcard-enabled pattern matching.
Supported wildcards in all SQL databases are:
- _: (single-character wildcard)
- %: (multi-character wildcard)
With jOOQ, the LIKE
predicate can be created from any column expression as such:
TITLE LIKE '%abc%' TITLE NOT LIKE '%abc%'
BOOK.TITLE.like("%abc%") BOOK.TITLE.notLike("%abc%")
Concatenating wildcards
A common practice is to conatenate wildcards to the actual expression. While concatenation is dangerous in plain SQL, it is safe when creating dynamic bind values using the DSL API:
-- Generated SQL is using a bind variable TITLE LIKE '%abc%' TITLE NOT LIKE '%abc%'
// abc might be user input BOOK.TITLE.like("%" + abc "%") BOOK.TITLE.notLike("%" + abc + "%")
Escaping operands with the LIKE predicate
Often, your pattern may contain any of the wildcard characters "_"
and "%"
, in case of which you may want to escape them. jOOQ does not automatically escape patterns in like()
and notLike()
methods. Instead, you can explicitly define an escape character as such:
TITLE LIKE '%The !%-Sign Book%' ESCAPE '!' TITLE NOT LIKE '%The !%-Sign Book%' ESCAPE '!'
BOOK.TITLE.like("%The !%-Sign Book%", '!') BOOK.TITLE.notLike("%The !%-Sign Book%", '!')
In the above predicate expressions, the exclamation mark character is passed as the escape character to escape wildcard characters "!_"
and "!%"
, as well as to escape the escape character itself: "!!"
Please refer to your database manual for more details about escaping patterns with the LIKE
predicate.
jOOQ's convenience methods using the LIKE predicate
jOOQ also provides a few convenience methods for common operations performed on strings using the LIKE
predicate. Typical operations are "contains predicates", "starts with predicates", "ends with predicates", etc.
-- case insensitivity LOWER(TITLE) LIKE LOWER('%abc%') LOWER(TITLE) NOT LIKE LOWER('%abc%') -- contains and similar methods TITLE LIKE '%' || 'abc' || '%' TITLE LIKE 'abc' || '%' TITLE LIKE '%' || 'abc'
// case insensitivity BOOK.TITLE.likeIgnoreCase("%abc%") BOOK.TITLE.notLikeIgnoreCase("%abc%") // contains and similar methods BOOK.TITLE.contains("abc") BOOK.TITLE.startsWith("abc") BOOK.TITLE.endsWith("abc")
Note, that jOOQ escapes %
and _
characters in values in some of the above predicate implementations. For simplicity, this has been omitted in this manual.
Dialect support
This example using jOOQ:
BOOK.TITLE.like("%abc%")
Translates to the following dialect specific expressions:
All dialects
BOOK.TITLE LIKE '%abc%'
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!