This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Quantified LIKE predicate (binary)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The binary synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators can be used to (positively resp. negatively) match a binary 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 CAST('%abc%' AS VARBINARY) OR
TITLE LIKE '%def%' AS VARBINARY))
(TITLE NOT LIKE CAST('%abc%' AS VARBINARY) OR
TITLE NOT LIKE CAST('%def%' AS VARBINARY))
(TITLE LIKE CAST('%abc%' AS VARBINARY) AND
TITLE LIKE CAST('%def%' AS VARBINARY))
(TITLE NOT LIKE CAST('%abc%' AS VARBINARY) AND
TITLE NOT LIKE CAST('%def%' AS VARBINARY))
BOOK.TITLE.binaryLike(
any("%abc%".getBytes(), "%def%".getBytes()))
BOOK.TITLE.notBinaryLike(
any("%abc%".getBytes(), "%def%".getBytes()))
BOOK.TITLE.binaryLike(
all("%abc%".getBytes(), "%def%".getBytes()))
BOOK.TITLE.notBinaryLike(
all("%abc%".getBytes(), "%def%".getBytes()))
Note that both the LIKE ANY and LIKE ALL predicates allow matching a binary 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.
See quantified LIKE predicate for a text version of this predicate.
Dialect support
This example using jOOQ:
BOOK.TITLE.binaryLike(any(select(binaryConcat(binaryConcat(val("%".getBytes()), LANGUAGE.CD.cast(VARBINARY)), val("%".getBytes()))).from(LANGUAGE)))
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
BOOK.TITLE LIKE ANY ( SELECT ((CAST(E'\\045' AS bytea) || CAST(LANGUAGE.CD AS bytea)) || CAST(E'\\045' AS bytea)) FROM LANGUAGE )
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!