SIMILAR TO predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
SIMILAR TO predicates are popular for more complex wildcard and regular expression enabled pattern matching. Supported wildcards in all SQL databases are:
- _: (single-character wildcard)
- %: (multi-character wildcard)
With jOOQ, the
SIMILAR TO predicate can be created from any column expression as such:
TITLE SIMILAR TO '%abc%' TITLE NOT SIMILAR TO '%abc%'
Often, your pattern may contain any of the wildcard characters
"%", in case of which you may want to escape them. jOOQ does not automatically escape patterns in
notSimilarTo() methods. Instead, you can explicitly define an escape character as such:
TITLE SIMILAR TO '%The !%-Sign Book%' ESCAPE '!' TITLE NOT SIMILAR TO '%The !%-Sign Book%' ESCAPE '!'
BOOK.TITLE.similarTo("%The !%-Sign Book%", '!') BOOK.TITLE.notSimilarTo("%The !%-Sign Book%", '!')
In the above predicate expressions, the exclamation mark character is passed as the escape character to escape wildcard characters
"!%", as well as to escape the escape character itself:
Please refer to your database manual for more details about escaping patterns with the
SIMILAR TO predicate as well as what regular expression syntax is supported.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- COCKROACHDB, FIREBIRD, POSTGRES, YUGABYTE BOOK.TITLE SIMILAR TO '%X%' -- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, BIGQUERY, DB2, DERBY, EXASOL, H2, HANA, HSQLDB, IGNITE, INFORMIX, MARIADB, -- MEMSQL, MYSQL, ORACLE, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, VERTICA /* UNSUPPORTED */