SQL Reference - OVERLAY

OVERLAY(string PLACING new_substring FROM position [ FOR count ])

string: This parameter specifies the original string where the replacement will take place.
placing new_substring: It indicates the substring which is to be placed in the original string.
from position: This parameter defines the starting point from where the replacement should be initiated in the original string.
for count: An optional argument that specifies the number of characters from the original string that should be replaced by the new_substring. If not specified, the length of new_substring is used.

Example

SELECT OVERLAY('PostgreSQL is great' PLACING 'fantastic' FROM 16 FOR 5);

Output

     overlay
------------------
 PostgreSQL is fantastic
(1 row)

Explanation

The OVERLAY function in the above example replaces a substring from a string. The substring ‘great’ (which is 5 characters long and starts at the 16th position in the original string ‘PostgreSQL is great’) is replaced with ‘fantastic’. Hence, the output ‘PostgreSQL is fantastic’.

OVERLAY(string PLACING replacement_string FROM start_position FOR string_length)

string: The original string on which the operation is to be performed.
replacement_string: The string that will replace a part of the original string.
start_position: The position within the original string where the replacement starts.
string_length: The length of the part of the original string that will be replaced with the replacement_string.

Example

SELECT OVERLAY('SQL Server' PLACING 'Developer' FROM 5 FOR 6) as Result;

Output

Developerer

Explanation

The OVERLAY function replaces a part of a string with another string. In the above example, the string “SQL Server” undergoes replacement from the 5th character till the 6th character, with the word “Developer”. Consequently, “Server” is replaced and ‘SQL Developerer’ becomes the modified string, as shown in the output.

OVERLAY(input_string PLACING replace_string FROM start_position [ FOR length ] )

input_string: The original string of characters where the replacement will occur.
replace_string: The string that will be inserted into the original string.
start_position: The position in the original string where the replacement will begin.
length: Optional. The number of characters in the original string that will be replaced by the replace_string. If length is not specified, default is the length of replace_string.

Example

SELECT OVERLAY('Oracle SQL' PLACING 'PL/SQL' FROM 8) AS "RESULT" FROM dual;

Output

RESULT
--------------------------------
Oracle PL/SQL

Explanation

The OVERLAY function is used to replace a specific portion of a string with another string. In the given example, the string ‘SQL’ starting from the 8th character in ‘Oracle SQL’ is replaced by ‘PL/SQL’. Therefore, the output is ‘Oracle PL/SQL’.