OVERLAY

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.

Note

If you specify USING OCTETS, Vertica calls the OVERLAYB function.

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)