Available in versions: Dev (3.20)
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
OVERLAY (binary)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The OVERLAY() function takes a binary string and "overlays it on top of another binary string".
SELECT overlay(cast('abcdefg' as bytea), cast('xxx' as bytea), 2);
create.select(binaryOverlay(val("abcdefg".getBytes()), "xxx".getBytes(), 2)).fetch();
The result being
+---------+ | overlay | +---------+ | axxxefg | +---------+
See OVERLAY for a text version of this function.
Dialect support
This example using jOOQ:
binaryOverlay(val("abcdefg".getBytes()), "xxx".getBytes(), 2)
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
overlay(CAST(E'\\141\\142\\143\\144\\145\\146\\147' AS bytea) PLACING CAST(E'\\170\\170\\170' AS bytea) FROM 2)
CockroachDB
((substring( CAST(E'\\141\\142\\143\\144\\145\\146\\147' AS bytea), 1, (2 - 1) ) || CAST(E'\\170\\170\\170' AS bytea)) || substring( CAST(E'\\141\\142\\143\\144\\145\\146\\147' AS bytea), (2 + length(CAST(E'\\170\\170\\170' AS bytea))) ))
Redshift
((substring( X'61626364656667', 1, (2 - 1) ) || X'787878') || substring( X'61626364656667', (2 + length(X'787878')) ))
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, DB2, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!