Quantified LIKE predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ also provides the synthetic [NOT] LIKE ANY
and [NOT] LIKE ALL
operators, which can be used to (positively resp. negatively) match a 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 '%abc%' OR TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' OR TITLE NOT LIKE '%def%') (TITLE LIKE '%abc%' AND TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' AND TITLE NOT LIKE '%def%')
BOOK.TITLE.like(any("%abc%", "%def%")) BOOK.TITLE.notLike(any("%abc%", "%def%")) BOOK.TITLE.like(all("%abc%", "%def%")) BOOK.TITLE.notLike(all("%abc%", "%def%"))
All corresponding Java methods Field.like(QuantifiedSelect)
and Field.notLike(QuantifiedSelect)
return an instance of LikeEscapeStep
, which can be used to specify an ESCAPE
clause that will be applied to all patterns in the list. For brevity the examples above don't show this.
Note that both the LIKE ANY
and LIKE ALL
predicates allow matching a 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
.
Dialect support
This example using jOOQ:
BOOK.TITLE.like(any(select(concat(val("%"), LANGUAGE.CD, val("%"))).from(LANGUAGE)))
Translates to the following dialect specific expressions:
ASE, DB2, Firebird, Sybase, Teradata
1 = ANY ( SELECT CASE WHEN BOOK.TITLE LIKE pattern THEN 1 WHEN NOT (BOOK.TITLE LIKE pattern) THEN 0 END FROM ( SELECT ('%' || LANGUAGE.CD || '%') pattern FROM LANGUAGE ) t )
Aurora MySQL, MariaDB, MySQL
TRUE = ANY ( SELECT (BOOK.TITLE LIKE pattern) FROM ( SELECT concat('%', LANGUAGE.CD, '%') pattern FROM LANGUAGE ) t )
Aurora Postgres, ClickHouse, CockroachDB, Postgres, Snowflake, YugabyteDB
BOOK.TITLE LIKE ANY ( SELECT ('%' || LANGUAGE.CD || '%') FROM LANGUAGE )
Derby, DuckDB, H2, HSQLDB, Oracle, Vertica
TRUE = ANY ( SELECT (BOOK.TITLE LIKE pattern) FROM ( SELECT ('%' || LANGUAGE.CD || '%') pattern FROM LANGUAGE ) t )
Hana
TRUE = ANY ( SELECT CASE WHEN BOOK.TITLE LIKE pattern THEN TRUE WHEN NOT (BOOK.TITLE LIKE pattern) THEN FALSE END FROM ( SELECT ('%' || LANGUAGE.CD || '%') pattern FROM LANGUAGE ) t )
Informix
CAST('t' AS boolean) = ANY ( SELECT CASE WHEN BOOK.TITLE LIKE pattern THEN CAST('t' AS boolean) WHEN NOT (BOOK.TITLE LIKE pattern) THEN CAST('f' AS boolean) END FROM ( SELECT ('%' || LANGUAGE.CD || '%') pattern FROM LANGUAGE ) t )
SQLServer
1 = ANY ( SELECT CASE WHEN BOOK.TITLE LIKE pattern THEN 1 WHEN NOT (BOOK.TITLE LIKE pattern) THEN 0 END FROM ( SELECT ('%' + LANGUAGE.CD + '%') pattern FROM LANGUAGE ) t )
Access, BigQuery, Exasol, MemSQL, Redshift, SQLDataWarehouse, SQLite, Trino
/* 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!