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, CUBRID, FIREBIRD, POSTGRES, VERTICA
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'))))

-- HANA, SYBASE
((substring('abcdefg', 1, (2 - 1)) || 'xxx') || substring('abcdefg', (2 + length('xxx'))))

-- HSQLDB, IGNITE, 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'))))

-- INGRES
((substring('abcdefg', CAST(1 AS integer), CAST((2 - 1) AS integer)) || 'xxx') || substring('abcdefg', CAST((2 + length('xxx')) AS integer)))

-- 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.15, see #10141)

The jOOQ User Manual : SQL building : Column expressions : String functions : OVERLAY

previous : next