Substring and overlay functions in PostgreSQL

Introduction

Substring and overlay functions help you extract or replace parts of text easily, like cutting or pasting pieces of a string.

You want to get a part of a word or sentence, like the first 3 letters of a name.

You need to replace a part of a text with something else, like fixing a typo inside a sentence.

You want to check or compare a small piece inside a bigger text.

You want to remove or change a section of a string without rewriting the whole thing.

Syntax

PostgreSQL

SUBSTRING(string FROM start FOR length)
OVERLAY(string PLACING new_substring FROM start FOR length)

Positions start at 1, not 0, so the first character is position 1.

If length is omitted in SUBSTRING, it takes all characters from start to the end.

Examples

Extracts the first 5 characters: 'Hello'.

PostgreSQL

SELECT SUBSTRING('Hello World' FROM 1 FOR 5);

Extracts from position 7 to the end: 'World'.

PostgreSQL

SELECT SUBSTRING('Hello World' FROM 7);

Replaces 'World' with 'Friend', resulting in 'Hello Friend'.

PostgreSQL

SELECT OVERLAY('Hello World' PLACING 'Friend' FROM 7 FOR 5);

Replaces 'bcd' with '123', resulting in 'a123ef'.

PostgreSQL

SELECT OVERLAY('abcdef' PLACING '123' FROM 2 FOR 3);

Sample Program

This program shows how to extract parts of a string and how to replace parts using substring and overlay functions.

PostgreSQL

SELECT 'Original text: Hello World' AS description;
SELECT SUBSTRING('Hello World' FROM 1 FOR 5) AS substring_example;
SELECT SUBSTRING('Hello World' FROM 7) AS substring_to_end;
SELECT OVERLAY('Hello World' PLACING 'Friend' FROM 7 FOR 5) AS overlay_example;
SELECT OVERLAY('abcdef' PLACING '123' FROM 2 FOR 3) AS overlay_partial;

Important Notes

Substring function runs in O(n) time where n is the length of the substring extracted.

Overlay also runs in O(n) time where n is the length of the string being modified.

Common mistake: forgetting that string positions start at 1, not 0.

Use substring when you want to get part of a string; use overlay when you want to replace part of a string.

Summary

Substring extracts a part of a string starting at a given position.

Overlay replaces part of a string with another string starting at a given position.

Positions start at 1, and length is optional in substring.