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)
-- DB2
overlay('abcdefg' PLACING 'xxx' FROM 2 FOR length('xxx'))
-- DERBY, ORACLE, SQLITE
((substr(
'abcdefg',
1,
(2 - 1)
) || 'xxx') || substr(
'abcdefg',
(2 + length('xxx'))
))
-- DUCKDB, HANA, SYBASE, TRINO
((substring(
'abcdefg',
1,
(2 - 1)
) || 'xxx') || substring(
'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.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!