| The jOOQ User Manual : SQL building : Column expressions : String functions : OVERLAY | previous : next |
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, MARIADB, MYSQL
insert('abcdefg', 2, char_length('xxx'), 'xxx')
-- AURORA_POSTGRES, 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'))))
-- H2, HSQLDB, REDSHIFT
((substring('abcdefg', 1, (2 - 1)) || 'xxx') || substring('abcdefg', (2 + char_length('xxx'))))
-- HANA, SYBASE
((substring('abcdefg', 1, (2 - 1)) || 'xxx') || substring('abcdefg', (2 + 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'))))
-- 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)
