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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook