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. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!