OVERLAY
Returns a VARCHAR value representing a string having had a substring replaced by another string.
Behavior Type
Immutable if using OCTETS, Stable otherwise
Syntax
OVERLAY ( expression1 PLACING expression2 FROM position ... [ FOR extent ] ... [ USING { CHARACTERS | OCTETS } ] )
Parameters
expression1 |
(CHAR or VARCHAR) is the string to process |
expression2 |
(CHAR or VARCHAR) is the substring to overlay |
position |
(INTEGER) is the character or octet position (counting from one) at which to begin the overlay |
extent |
(INTEGER) specifies the number of characters or octets to replace with the overlay |
USING CHARACTERS | OCTETS |
Determines whether OVERLAY uses characters (the default) or octets |
Examples
=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2); overlay ----------- 1xxx56789 (1 row)
=> SELECT OVERLAY('123456789' PLACING 'XXX' FROM 2 USING OCTETS); overlayb ----------- 1XXX56789 (1 row)
=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 4); overlay ---------- 1xxx6789 (1 row)
=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 5); overlay --------- 1xxx789 (1 row)
=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 6); overlay --------- 1xxx89 (1 row)