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',
  1,
  (2 - 1)
) || 'xxx') & mid(
  'abcdefg',
  (2 + len('xxx'))
))((mid(
  'abcdefg',
  1,
  (2 - 1)
) & 'xxx')(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'))
))

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

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

previous : next