PostgreSQL OVERLAY Function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

Name Description
main_string The string on which function will work.
replacing_string The string which will be replaced.
starting_position The position from where the replacement will start.
number_of_characters The replacement string containing the number of characters. It is optional.

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.

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

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.

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

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.

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

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.

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

Output :

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