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%' TITLE NOT SIMILAR TO '%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
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
"!%", 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.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- All dialects BOOK.TITLE SIMILAR TO '%X%'