Substring and overlay functions in PostgreSQL - Step-by-Step Execution

Concept Flow - Substring and overlay functions

Start with original string

↓

Apply substring function

↓

Extract part of string

↓

Apply overlay function

↓

Replace part of string with new substring

↓

Return modified string

The flow shows extracting a part of a string using substring, then replacing part of a string using overlay, resulting in a modified string.

Execution Sample

PostgreSQL

SELECT substring('Hello World' FROM 7 FOR 5) AS extracted;
SELECT overlay('Hello World' PLACING 'Earth' FROM 7 FOR 5) AS replaced;

Extracts 'World' from 'Hello World' and replaces 'World' with 'Earth' in the string.

Execution Table

Step	Function	Input String	Parameters	Intermediate Result	Output
1	substring	'Hello World'	FROM 7 FOR 5	Extract characters 7 to 11	'World'
2	overlay	'Hello World'	PLACING 'Earth' FROM 7 FOR 5	Replace characters 7 to 11 with 'Earth'	'Hello Earth'
3	overlay	'Hello World'	PLACING 'Mars' FROM 7 FOR 3	Replace characters 7 to 9 with 'Mars'	'Hello Marsld'
4	substring	'Hello World'	FROM 1 FOR 5	Extract characters 1 to 5	'Hello'
5	overlay	'Hello World'	PLACING 'Hi' FROM 1 FOR 5	Replace characters 1 to 5 with 'Hi'	'Hi World'
6	substring	'Hello World'	FROM 12 FOR 3	Start position beyond string length	'' (empty string)
7	overlay	'Hello World'	PLACING 'Test' FROM 12 FOR 3	Start position beyond string length, append 'Test'	'Hello WorldTest'
8	overlay	'Hello World'	PLACING 'X' FROM 7 FOR 0	Insert 'X' at position 7 without removing	'Hello XWorld'
9	substring	'Hello World'	FROM 0 FOR 5	Start position 0 treated as 1	'Hello'
10	overlay	'Hello World'	PLACING 'Y' FROM 0 FOR 3	Start position 0 treated as 1, replace first 3 chars	'Ylo World'

💡 All steps complete showing substring extraction and overlay replacement with various parameters.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	After Step 8	After Step 9	After Step 10
input_string	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'	'Hello World'
substring_result	NULL	'World'	'World'	'World'	'Hello'	'Hello'	''	''	''	'Hello'	'Hello'
overlay_result	NULL	NULL	'Hello Earth'	'Hello Marsld'	NULL	'Hi World'	NULL	'Hello WorldTest'	'Hello XWorld'	NULL	'Ylo World'

Key Moments - 3 Insights

Why does substring return an empty string when the start position is beyond the string length?

Because substring tries to extract characters starting beyond the string's end, it returns an empty string as shown in execution_table row 6.

How does overlay behave when the start position is beyond the string length?

Overlay appends the new substring at the end if the start position is beyond the string length, as seen in execution_table row 7.

What happens if the overlay length is zero?

Overlay inserts the new substring at the start position without removing any characters, demonstrated in execution_table row 8.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output of substring at step 4?

A 'World'

B '' (empty string)

C 'Hello'

D 'Earth'

💡 Hint

Check the 'Output' column for step 4 in the execution_table.

At which step does overlay append the new substring because the start position is beyond the string length?

A Step 6

B Step 7

C Step 8

D Step 10

💡 Hint

Look for overlay steps where the start position is greater than string length in execution_table.

If overlay is called with PLACING 'X' FROM 7 FOR 0, what is the output string?

A 'Hello XWorld'

B 'Hello World'

C 'Hello Earth'

D 'Hello Marsld'

💡 Hint

Refer to execution_table row 8 for overlay with length zero.

Concept Snapshot

substring(string FROM start FOR length) extracts part of string.
overlay(string PLACING new_substring FROM start FOR length) replaces part of string.
If start is beyond string length, substring returns empty, overlay appends.
Overlay with length 0 inserts without removing.
Positions start at 1; 0 treated as 1.
Use these to extract or replace parts of text easily.

Full Transcript

This lesson shows how PostgreSQL substring and overlay functions work step-by-step. Substring extracts a part of a string starting at a given position for a given length. Overlay replaces part of a string starting at a position for a length with a new substring. If the start position is beyond the string length, substring returns an empty string, while overlay appends the new substring at the end. Overlay with length zero inserts the new substring without removing characters. Positions start at 1; if 0 is given, it is treated as 1. The execution table traces these behaviors with examples, and the variable tracker shows how results change after each step. Key moments clarify common confusions about positions and behavior when parameters are out of string bounds.