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)