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, 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.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo