Summary
ClickHouse does not support the OVERLAY function, which replaces a substring within a string at a specified position and length. It is part of the standard SQL string function set and is supported by PostgreSQL, Oracle, DuckDB, and others. See the PostgreSQL documentation on string functions for reference.
Current Behavior
SELECT OVERLAY('Hello World' PLACING 'SQL' FROM 7 FOR 5);
-- Code: 62. DB::Exception: Syntax errorExpected Behavior
SELECT OVERLAY('Hello World' PLACING 'SQL' FROM 7 FOR 5);
-- Returns: 'Hello SQL'
SELECT OVERLAY('abcdef' PLACING 'XY' FROM 3 FOR 2);
-- Returns: 'abXYef'
-- Without FOR clause — replaces exactly length(replacement) characters:
SELECT OVERLAY('abcdef' PLACING 'XY' FROM 3);
-- Returns: 'abXYef'
-- Empty replacement = deletion:
SELECT OVERLAY('Hello World' PLACING '' FROM 6 FOR 6);
-- Returns: 'Hello'Semantics: OVERLAY(s PLACING r FROM p FOR n) ≡ concat(substring(s, 1, p-1), r, substring(s, p+n)).
Workaround
SELECT concat(
substring('Hello World', 1, 6),
'SQL',
substring('Hello World', 12)
);Version info
Summary
ClickHouse does not support the
OVERLAYfunction, which replaces a substring within a string at a specified position and length. It is part of the standard SQL string function set and is supported by PostgreSQL, Oracle, DuckDB, and others. See the PostgreSQL documentation on string functions for reference.Current Behavior
Expected Behavior
Semantics:
OVERLAY(s PLACING r FROM p FOR n)≡concat(substring(s, 1, p-1), r, substring(s, p+n)).Workaround
Version info
OVERLAY(... PLACING ... FROM ... FOR ...)syntax #10168126.4.1.769