Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
LIKE IGNORE CASE predicate
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The LIKE IGNORE CASE predicate is a special case of the LIKE predicate, where the operand case is ignored.
-- case insensitivity TITLE ILIKE '%abc%' TITLE NOT ILIKE '%abc%'
// case insensitivity
BOOK.TITLE.likeIgnoreCase("%abc%")
BOOK.TITLE.notLikeIgnoreCase("%abc%")
While some dialects have native support for this operator via ILIKE or similar, most dialects can emulate the behaviour using the LOWER function.
Dialect support
This example using jOOQ:
BOOK.TITLE.likeIgnoreCase("%abc%")
Translates to the following dialect specific expressions:
Access
lcase(BOOK.TITLE) LIKE lcase('%abc%')
ASE, Aurora MySQL, BigQuery, DB2, Firebird, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica
lower(BOOK.TITLE) LIKE lower('%abc%')
Aurora Postgres, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, H2, Postgres, Redshift, Snowflake, YugabyteDB
BOOK.TITLE ILIKE '%abc%'
Generated with jOOQ 3.22. 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!