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 | +---------+
See OVERLAY (binary) for a binary version of this function.
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, Databricks, 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.21. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!