Replace a range of characters in a string with a replacement substring (SQL-standard PLACING/FROM/FOR syntax).
OVERLAY(str PLACING replacement FROM start [FOR count])## Overview Replaces a range of characters in the input string with a replacement substring. The range starts at the 1-based position `start` and extends for `count` characters. When `count` is omitted, it defaults to the character length of the replacement, which keeps the result the same length as the original for equal-length substitutions. OVERLAY is the SQL-standard splice operation and is the tool of choice for fixed-position edits: masking a middle section of an identifier, inserting a formatting character without removing any data, or patching a single-character field inside a fixed-layout record. ## Behavior - Returns NULL when any argument is NULL. - The start position is 1-based. Position 1 refers to the first character. - If `start` is greater than the input length, the replacement is appended at the end. - If `count` is 0, the replacement is inserted without removing any characters (pure insertion). - If `count` extends past the end of the input, the operation truncates at the input's end. - The result length may differ from the input when the replacement length differs from `count`. - Operates on Unicode code points. ## Compatibility - Fully conforms to the SQL-standard OVERLAY syntax with PLACING/FROM/FOR clauses.
| Name | Type | Description |
|---|---|---|
str | Specifies the input string in which the replacement occurs. | |
replacement | Specifies the replacement substring to splice into the input. | |
start | Specifies the 1-based starting position where the replacement begins. | |
count | Specifies how many characters of the original string to remove before inserting. Defaults to the character length of the replacement. Pass 0 to insert without deletion. |
-- Replace two characters at position 3
SELECT OVERLAY('abcdef' PLACING 'XX' FROM 3 FOR 2); -- 'abXXef'-- Insert (FOR 0) without removing anything
SELECT OVERLAY('abcdef' PLACING 'XX' FROM 3 FOR 0); -- 'abXXcdef'-- Default count equals replacement length
SELECT OVERLAY('abcdef' PLACING 'XX' FROM 3); -- 'abXXef'-- Replacement length differs from cut length
SELECT OVERLAY('abcdef' PLACING 'X' FROM 2 FOR 4); -- 'aXf'-- NULL propagates
SELECT OVERLAY(CAST(NULL AS VARCHAR) PLACING 'X' FROM 1 FOR 1); -- NULL-- Mask the middle of an account number for display
SELECT OVERLAY(account_number PLACING REPEAT('*', 8) FROM 5 FOR 8) AS masked
FROM retail.customers.profiles;