OVERLAY

Replaces part of a string with another string and returns the new string value as a VARCHAR.

Replaces part of a string with another string and returns the new string value as a VARCHAR.

Behavior type

Immutable if using OCTETS, Stable otherwise

Syntax

OVERLAY ( input-string PLACING replace-string FROM position [ FOR extent ] [ USING { CHARACTERS | OCTETS } ] )

Arguments

*input-string*
The string to process, of type CHAR or VARCHAR.
*replace-string*
The string to replace the specified substring of input-string, of type CHAR or VARCHAR.
*position*
Integer ≥1 that specifies the first character or octet of input-string to overlay replace-string.
*extent*
Integer that specifies how many characters or octets of input-string to overlay with replace-string. If omitted, OVERLAY uses the length of replace-string.

For example, compare the following calls to OVERLAY:

  • OVERLAY omits FOR clause. The number of characters replaced in the input string equals the number of characters in replacement string ABC:

    
    dbadmin=> SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5);
      overlay
    -----------
     1234ABC89
    (1 row)
    
  • OVERLAY includes a FOR clause that specifies to replace four characters in the input string with the replacement string. The replacement string is three characters long, so OVERLAY returns a string that is one character shorter than the input string:

    => SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5 FOR 4);
     overlay
    ----------
     1234ABC9
    (1 row)
    
  • OVERLAY includes a FOR clause that specifies to replace -2 characters in the input string with the replacement string. The function returns a string that is two characters longer than the input string:

    => SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5 FOR -2);
        overlay
    ----------------
     1234ABC3456789
    (1 row)
    
USING CHARACTERS | OCTETS
Specifies whether OVERLAY uses characters (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)