Name

OVERLAY

The OVERLAY function embeds one string into another and returns the result.

ANSI SQL Standard Syntax

OVERLAY(string PLACING embedded_string FROM start
[FOR length])

If any of the inputs are NULL, the OVERLAY function returns NULL. The embedded_string replaces the length characters in string, starting at the character position start. If the length is not specified, the embedded_string will replace all the characters in string after start.

MySQL, Oracle, and SQL Server

These platforms do not support the OVERLAY function. You can simulate the OVERLAY function on these platforms by using a combination of SUBSTRING and the concatenation operator.

PostgreSQL

PostgreSQL supports the ANSI standard for OVERLAY.

Examples

This is an example of how to use the OVERLAY function:

/* ANSI SQL and PostgreSQL */
SELECT OVERLAY('DONALD DUCK' PLACING 'TRUMP' FROM 8) FROM NAMES;
'DONALD TRUMP'

Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial