New versions: Dev (3.14) | Latest (3.13) | 3.12

SIMILAR TO predicate

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

SIMILAR TO predicates are popular for more complex wildcard and regular expression enabled pattern matching. Supported wildcards in all SQL databases are:

  • _: (single-character wildcard)
  • %: (multi-character wildcard)

With jOOQ, the SIMILAR TO predicate can be created from any column expression as such:

TITLE     SIMILAR TO '%abc%'

Escaping operands with the SIMILAR TO 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 similarTo() and notSimilarTo() methods. Instead, you can explicitly define an escape character as such:

TITLE     SIMILAR TO '%The !%-Sign Book%' ESCAPE '!'
TITLE NOT SIMILAR TO '%The !%-Sign Book%' ESCAPE '!'
BOOK.TITLE.similarTo("%The !%-Sign Book%", '!')
BOOK.TITLE.notSimilarTo("%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 SIMILAR TO predicate as well as what regular expression syntax is supported.

The jOOQ Logo