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

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.

Quantified LIKE predicate

In addition to the above, jOOQ also provides the synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators, which can be used to (positively resp. negatively) match a string against multiple patterns without having to manually string together multiple [NOT] LIKE predicates with AND or OR (learn about other synthetic sql syntaxes). The following examples show how these synthetic predicates translate to SQL:

(TITLE     LIKE '%abc%'  OR TITLE     LIKE '%def%')
(TITLE NOT LIKE '%abc%'  OR TITLE NOT LIKE '%def%')
(TITLE     LIKE '%abc%' AND TITLE     LIKE '%def%')
(TITLE NOT LIKE '%abc%' AND TITLE NOT LIKE '%def%')
BOOK.TITLE.like(any("%abc%", "%def%"))
BOOK.TITLE.notLike(any("%abc%", "%def%"))
BOOK.TITLE.like(all("%abc%", "%def%"))
BOOK.TITLE.notLike(all("%abc%", "%def%"))

All corresponding Java methods Field.like(QuantifiedSelect) and Field.notLike(QuantifiedSelect) return an instance of LikeEscapeStep, which can be used to specify an ESCAPE clause that will be applied to all patterns in the list. For brevity the examples above don't show this.

Note that both the LIKE ANY and LIKE ALL predicates allow matching a string against an empty list of patterns. For example, in the case of LIKE ANY this is equivalent to a 1 = 0 predicate and in the case of NOT LIKE ALL this behaves like 1 = 1.

Dialect support

This example using jOOQ:

BOOK.TITLE.like("%abc%")

Translates to the following dialect specific expressions:

All dialects

BOOK.TITLE LIKE '%abc%'

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo