OVERLAY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The OVERLAY()
function takes a string and "overlays it on top of another string".
SELECT overlay('abcdefg', 'xxx', 2);
create.select(overlay(val("abcdefg"), "xxx", 2)).fetch();
The result being
+---------+ | overlay | +---------+ | axxxefg | +---------+
Dialect support
This example using jOOQ:
overlay(val("abcdefg"), "xxx", 2)
Translates to the following dialect specific expressions:
Access
((mid( 'abcdefg', 1, (2 - 1) ) & 'xxx') & mid( 'abcdefg', (2 + len('xxx')) ))
ASE
((substring( 'abcdefg', 1, (2 - 1) ) || 'xxx') || substring( 'abcdefg', (2 + char_length('xxx')), 2147483647 ))
Aurora MySQL, Exasol, H2, MariaDB, MySQL
insert( 'abcdefg', 2, char_length('xxx'), 'xxx' )
Aurora Postgres, BigQuery, CockroachDB, Firebird, Postgres, Vertica, YugabyteDB
overlay('abcdefg' PLACING 'xxx' FROM 2)
ClickHouse, DuckDB, Hana, Sybase, Trino
((substring( 'abcdefg', 1, (2 - 1) ) || 'xxx') || substring( 'abcdefg', (2 + length('xxx')) ))
DB2
overlay('abcdefg' PLACING 'xxx' FROM 2 FOR length('xxx'))
Derby, Oracle, SQLite
((substr( 'abcdefg', 1, (2 - 1) ) || 'xxx') || substr( 'abcdefg', (2 + length('xxx')) ))
HSQLDB, Redshift
((substring( 'abcdefg', 1, (2 - 1) ) || 'xxx') || substring( 'abcdefg', (2 + char_length('xxx')) ))
Informix
((substr( 'abcdefg', 1, (2 - 1) ) || 'xxx') || substr( 'abcdefg', (2 + char_length('xxx')) ))
MemSQL
concat( concat( substring( 'abcdefg', 1, (2 - 1) ), 'xxx' ), substring( 'abcdefg', (2 + char_length('xxx')) ) )
Snowflake
insert( 'abcdefg', 2, length('xxx'), 'xxx' )
SQLDataWarehouse, SQLServer
((substring( 'abcdefg', 1, (2 - 1) ) + 'xxx') + substring( 'abcdefg', (2 + len('xxx')), 2147483647 ))
Teradata
((substring('abcdefg' FROM 1 FOR (2 - 1)) || 'xxx') || substring('abcdefg' FROM (2 + length('xxx'))))
(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!