PostgreSQL overlay() Function

The PostgreSQL overlay() function replaces the specified number of characters starting at the specified position with the specified text in a string.

overlay() Syntax

This is the syntax of the PostgreSQL overlay() function:

overlay(string PLACING replacement FROM start [FOR count])

Parameters

string

Required. The string to replace in.

replacement

Required. The string to replace with.

start

Required. Integer. The index position where the substitution starts.

count

Optional. Integer. The number of characters to be replaced. The default is the number characters in replacement, that is length(replacement).

Return value

The PostgreSQL overlay() function returns a string in which the specified number of characters starting at the specified position are replaced with the specified content.

If the parameter is NULL, the function will return NULL.

overlay() Examples

This following example shows how to use the overlay() function to modify the string Hello Tim to Hi Tim.

SELECT overlay('Hello Tim' PLACING 'Hi' FROM 1 FOR 5);

 overlay
---------
 Hi Tim

This following example shows how to use the overlay() function to modify the string Hello Tim to Hi Adam.

SELECT overlay('Hello Tim' PLACING 'Adam' FROM 7);

  overlay
------------
 Hello Adam

Here, we do not specify FOR count in overlay(), so the count is the length of Adam, that is 4.