SQL PLACING

What is the SQL PLACING keyword?

Keyword used inside the OVERLAY expression to mark the substring that will replace part of the original string.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PLACING: PostgreSQL, CockroachDB, Amazon Redshift, IBM Db2, HSQLDB, DuckDB, Standard SQL compliant engines. Not supported natively in MySQL, SQL Server, or SQLite.

SQL PLACING Full Explanation

PLACING is a reserved keyword defined in the SQL standard and implemented by PostgreSQL and several other databases to build an OVERLAY expression. OVERLAY performs in-place string substitution: it takes a source string, inserts (or overlays) a replacement substring beginning at a specified start position, and optionally for a specified length. The word PLACING separates the source string from the replacement substring, making the syntax self-describing. Unlike the REPLACE function, which substitutes all occurrences, OVERLAY with PLACING modifies the string only at the given position, giving developers precise, index-based control over string editing. Because PLACING is part of the SQL grammar rather than a standalone command, it appears only within the broader OVERLAY expression and cannot be executed by itself.

SQL PLACING Syntax

OVERLAY ( <source_string>
         PLACING <replacement_string>
         FROM <start_position>
         [ FOR <length> ]
       )

SQL PLACING Parameters

source_string (STRING) - The original text to be modified.
replacement_string (STRING) - The text that will be inserted.
start_position (INTEGER) - 1-based character position where replacement starts.
length – INTEGER (optional) - Number of characters to overwrite. If omitted, the replacement inserts without deleting.

Example Queries Using SQL PLACING

-- Replace 3 characters starting at position 5
SELECT OVERLAY('GalaxyRocks' PLACING ' SQL' FROM 5 FOR 3);

-- Insert a substring without overwriting
SELECT OVERLAY('2024-06-01' PLACING '/' FROM 5);

-- Use in an UPDATE
UPDATE users
SET email = OVERLAY(email PLACING '.old' FROM POSITION('@' IN email))
WHERE created_at < '2020-01-01';

Expected Output Using SQL PLACING

First query returns 'Gala SQL Rocks'
Second query returns '2024/-06-01'
Update query appends '
old' right before the @ in legacy email addresses, modifying matching rows

Use Cases with SQL PLACING

Insert or overwrite characters at a known index in a string
Mask sensitive data by replacing part of a value (e.g., credit-card middle digits)
Reformat dates or identifiers without full string reconstruction
Build dynamic UPDATE statements that tweak only specific portions of text columns

Common Mistakes with SQL PLACING

Forgetting the FROM clause; PLACING must be followed by FROM position.
Using 0-based indexes; SQL positions start at 1.
Assuming it behaves like REPLACE and affects every match.
Omitting FOR yet expecting existing characters to be removed.

First Introduced In

SQL:1999

Frequently Asked Questions

What does PLACING do in an OVERLAY expression?

PLACING tells the database which substring you intend to insert or overwrite into the source string. It pairs with FROM to define the exact replacement location.

Is PLACING mandatory when using OVERLAY?

Yes. The keyword is part of the required grammar. Without PLACING the parser cannot distinguish the replacement substring from the source string.

Can I use PLACING on its own like a function?

No. PLACING only appears inside the OVERLAY expression. It has no standalone behavior.

How do I mimic PLACING in databases that lack OVERLAY?

You can concatenate SUBSTRING pieces: CONCAT(SUBSTRING(src,1,pos-1), replacement, SUBSTRING(src,pos+len)). That replicates the PLACING effect.