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!