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%'
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 + "%")
Often, your pattern may contain any of the wildcard characters
"%", in case of which you may want to escape them. jOOQ does not automatically escape patterns in
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
"!%", as well as to escape the escape character itself:
Please refer to your database manual for more details about escaping patterns with the
In addition to the above, jOOQ 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. Here is the full convenience API wrapping
-- 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
_ characters in value in some of the above predicate implementations. For simplicity, this has been omitted in this manual.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- All dialects BOOK.TITLE LIKE '%abc%'