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, CockroachDB, Firebird, Postgres, Vertica, YugabyteDB
overlay('abcdefg' PLACING 'xxx' FROM 2)
BigQuery, HSQLDB, Redshift
(substring( 'abcdefg', 1, (2 - 1) ) || 'xxx' || substring( 'abcdefg', (2 + char_length('xxx')) ))
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')) ))
Informix
(substr( 'abcdefg', 1, (2 - 1) ) || 'xxx' || substr( 'abcdefg', (2 + char_length('xxx')) ))
MemSQL
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'))))
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!