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%'
BOOK.TITLE.similarTo("%abc%")
BOOK.TITLE.notSimilarTo("%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.
Dialect support
This example using jOOQ:
BOOK.TITLE.similarTo("%X%")
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, CockroachDB, DuckDB, Exasol, Firebird, Postgres, Snowflake, YugabyteDB
BOOK.TITLE SIMILAR TO '%X%'
ASE, Access, Aurora MySQL, ClickHouse, DB2, Derby, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!