Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

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!

The jOOQ Logo