PostgreSQL OVERLAY() function

OVERLAY() function

The PostgreSQL overlay function is used to replace a specified text or string in place of a text or substring within a mother string. The replacement substring mentioned by the position, from where the replacement substring will start and a number of characters mentioned for the replacement from the specified position.

Syntax:

overlay(<main_string> placing <replacing_string> 
from <starting_position> [ for <mumber_of_characters>] )

Parameters

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL OVERLAY() function

Example - 1:

In the example below, the string 'resou' replaces 5 consecutive characters of the string 'w3333333rce', starting from the third character from the left. Thus we get the result "w3resou3rce". Notice that, since we have not mentioned the second parameter, i.e. how many characters to be replaced, it replaces the number of characters equal to the number of characters present in the replacing_string, which is five.

Code:

SELECT overlay('w3333333rce' placing 'resou' from 3);

Sample Output:

   overlay
-------------
 w3resou3rce
(1 row)

Example - 2:

In the example below, four consecutive characters from the third position have been replaced by the string 'resou'. Thus we get the result "w3resou33rce". Notice that, here we have mentioned the second parameter, i.e. how many characters to be replaced, it is four, so it replaces four characters from the third position of the main_string.

Code:

SELECT overlay('w3333333rce' placing 'resou' from 3 for 4); 
 

Sample Output:

   overlay
--------------
 w3resou33rce
(1 row)

Example - 3:

In the example below, five consecutive characters from the third position have been replaced by the string 'resou'. Thus we get the result "w3resource". Notice that here we have mentioned the second parameter, i.e. how many characters to be replaced, it is five, so it replaces five characters from the third position of the main_string.

Code:

SELECT overlay('w333333rce' placing 'resou' from 3 for 5);

Sample Output:

  overlay
------------
 w3resource
(1 row)

Example - 4:

In the example below, six consecutive characters from the third position have been replaced by the string 'resou' which containing five characters. Thus we get the result "w3resouce". Notice that here we have mentioned the second parameter, i.e. how many characters to be replaced, it is six, it is more than the replacing_string, so it replaces six characters from the third position of the main_string.

Code:

SELECT overlay('w333333rce' placing 'resou' from 3 for 6);  

Sample Output:

  overlay
-----------
 w3resouce
(1 row)

Previous: OCTET_LENGTH function
Next: POSITION function