OVERLAYB
Replaces part of a string with another string and returns the new string as an octet value.
Replaces part of a string with another string and returns the new string as an octet value.
The OVERLAYB function treats the multibyte character string as a string of octets (bytes) and use octet numbers as incoming and outgoing position specifiers and lengths. The strings themselves are type VARCHAR, but they treated as if each byte was a separate character.
Behavior type
ImmutableSyntax
OVERLAYB ( input-string, replace-string, position [, extent ] )
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 octet of*
input-string* to overlayreplace-string. extent- Integer that specifies how many octets of
input-stringto overlay withreplace-string. If omitted, OVERLAY uses the length ofreplace-string.
Examples
=> SELECT OVERLAYB('123456789', 'ééé', 2);
OVERLAYB
----------
1ééé89
(1 row)
=> SELECT OVERLAYB('123456789', 'ßßß', 2);
OVERLAYB
----------
1ßßß89
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2);
OVERLAYB
-----------
1xxx56789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 4);
OVERLAYB
----------
1xxx6789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 5);
OVERLAYB
----------
1xxx789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 6);
OVERLAYB
----------
1xxx89
(1 row)