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')))) -- 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)
Feedback
Do you have any feedback about this page? We'd love to hear it!