overlay() function in PostgreSQL

The overlay() function in PostgreSQL is used to replace a substring of a string with another substring. This function is useful when you need to modify part of a string by inserting or replacing specific characters at a given position.

Syntax – Overlay() function in PostgreSQL

• string: The original string to be modified.
• replacement: The string that will replace a portion of the original string.
• start: The position in the original string where the replacement will begin
• length (optional): The number of characters in the original string to replace. If not specified, the replacement string will be inserted at the specified position.

Examples

1. Basic Example: Replace a Substring

 

SELECT OVERLAY('Astrology' PLACING 'nomy' FROM 6 ) AS result;

• Explanation: The substring starting from position 6 (‘logy’) is replaced with ‘nomy’

Result:

2. Example with Specified Length: Replace Specific Number of Characters

 

SELECT OVERLAY('abcdef' PLACING 'XY' FROM 3 FOR 2) AS result;

• Explanation: Starting from position 3, two characters (‘cd’) are replaced with ‘XY’

Result:

3. Insert a String without Removing Any Characters:

If you want to insert a substring without removing any characters from the original string, you can give 0 value to the FOR clause:

 

SELECT OVERLAY('abcdef' PLACING 'XYZ' FROM 4) AS result;

• Explanation: The string ‘XYZ’ is inserted at position 4, without replacing any characters starting from position 4. At 4^th position ‘XYZ’ is inserted without replacing already existing ‘d’ at 4^th position.

Result:

To use overlay() function on a Column in PostgreSQL:

You can use overlay() function to replace the string value of a column with another string value.

Let’s use the table name “documents”

 

SELECT *, OVERLAY(text_content PLACING 'swift' FROM 6 FOR 5) AS updated_text
FROM documents;

• Explanation:  In the first row, ‘quick’ is replaced with ‘swift’, and in the second row, ‘along’ is replaced with ‘swift’.

Output: