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.

This function is particularly useful for modifying specific parts of strings based on positional parameters. By specifying the starting position and the length of the segment to be replaced, you can precisely control the text substitution.

Uses of OVERLAY() Function

• String Modification: Modify specific parts of a string without altering the entire string.


• Data Cleaning: Clean and standardize text data by replacing unwanted substrings.


• Template Processing: Replace placeholders within template strings with actual values.


• Dynamic Text Generation: Generate dynamic text content by overlaying variable data onto static strings.


• Conditional Updates: Update parts of a string conditionally based on business logic.


• Text Correction: Correct specific substrings within larger text fields.

Syntax:

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

Parameters

PostgreSQL Version: 9.3

• Compatible with PostgreSQL version 9.3 and later.

Pictorial Presentation of PostgreSQL OVERLAY() function

Example 1: Basic Replacement

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.

SQL Code:

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

Output:

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

Example 2: Specifying Number of Characters to Replace

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.

SQL Code:

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

Output:

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

Example 3: Exact Replacement Length

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.

SQL Code:

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

Output:

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

Example 4: More Characters to Replace Than Available in Replacing String

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.

SQL Code:

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

Output:

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

Example 5: Using OVERLAY() with Dynamic Data

You can use the OVERLAY() function to dynamically replace parts of a string based on data from a table. Consider a table 'documents' below:

Sample table documents:

document_id|content        |
-----------+---------------+
          1|content1       |
          2|Another content|
          3|A newr content |

If we want to update a portion of each documents's content, we can use the OVERLAY() function as follows:

SQL Code:

UPDATE documents
SET content = overlay(content placing 'updated text' from 5 for 10)
WHERE document_id = 2; 

Here is the table after update the 'documents' table:

document_id|content          |
-----------+-----------------+
          1|content1         |
          3|A newr content   |
          2|Anotupdated textt|

Example 6: Using with CONCAT() Function

SQL Code:

SELECT overlay(CONCAT('Hello', 'World') placing 'There' from 6 for 5); 

Output:

overlay   |
----------+
HelloThere|

Example 7: Using with SUBSTRING() Function

SQL Code:

SELECT overlay(SUBSTRING('HELLO WORLD' FROM 1 FOR 5) placing 'Y' from 2 for 1);

Output:

overlay|
-------+
HYLLO  |

Previous: OCTET_LENGTH function
Next: POSITION function