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%'
Escaping operands with the SIMILAR TO predicate
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:
-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DUCKDB, EXASOL, FIREBIRD, POSTGRES, SNOWFLAKE, YUGABYTEDB BOOK.TITLE SIMILAR TO '%X%' -- ACCESS, ASE, AURORA_MYSQL, DB2, DERBY, H2, HANA, HSQLDB, INFORMIX, MARIADB, MEMSQL, MYSQL, ORACLE, REDSHIFT, -- SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA /* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Do you have any feedback about this page? We'd love to hear it!