Substring and overlay functions in PostgreSQL - Mini Project: Build & Apply

Using Substring and Overlay Functions in PostgreSQL

📖 Scenario: You work in a customer service department. You have a database table with customer feedback comments. Some comments contain sensitive words that need to be replaced with asterisks for privacy before sharing the data.

🎯 Goal: Build a PostgreSQL query that extracts parts of the comments using the substring function and replaces sensitive words using the overlay function.

📋 What You'll Learn

Create a table called feedback with columns id (integer) and comment (text).

Insert three rows with specific comments containing the word 'secret'.

Create a variable start_pos to hold the starting position for substring extraction.

Write a query using substring(comment from start_pos for 10) to extract a 10-character snippet from each comment.

Use the overlay function to replace the word 'secret' with '******' in the comments.

💡 Why This Matters

🌍 Real World

Cleaning sensitive information from customer feedback before sharing with teams or publishing.

💼 Career

Database administrators and analysts often need to manipulate text data to protect privacy and extract useful information.

1

Create the feedback table and insert data

Create a table called feedback with columns id (integer) and comment (text). Insert these exact rows: (1, 'This is a secret message'), (2, 'No secrets here'), (3, 'Keep this secret safe').

PostgreSQL

-- Create table feedback and insert rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the starting position for substring extraction

Create a variable called start_pos and set it to 9. This will be the starting position for extracting substrings from the comments.

PostgreSQL

CREATE TABLE feedback (id INTEGER, comment TEXT);
INSERT INTO feedback (id, comment) VALUES
(1, 'This is a secret message'),
(2, 'No secrets here'),
(3, 'Keep this secret safe');

-- Create a variable start_pos and set it to 9
-- Your code here

Need a hint?

Use the psql command \set to define a variable.

3

Extract a substring from comments using the start_pos variable

Write a SELECT query that extracts a 10-character substring from the comment column starting at position :start_pos. Use substring(comment from :start_pos for 10) and select id and this substring as snippet.

PostgreSQL

CREATE TABLE feedback (id INTEGER, comment TEXT);
INSERT INTO feedback (id, comment) VALUES
(1, 'This is a secret message'),
(2, 'No secrets here'),
(3, 'Keep this secret safe');

\set start_pos 9

-- Write a SELECT query to extract a 10-character substring from comment starting at :start_pos
-- Your code here

Need a hint?

Use substring(comment from :start_pos for 10) in the SELECT clause.

4

Replace the word 'secret' with '******' using overlay

Write a SELECT query that uses the overlay function to replace the word 'secret' with '******' in the comment column. Replace starting at the position where 'secret' first appears using position('secret' in comment) and replace 6 characters. Select id and the modified comment as clean_comment.

PostgreSQL

CREATE TABLE feedback (id INTEGER, comment TEXT);
INSERT INTO feedback (id, comment) VALUES
(1, 'This is a secret message'),
(2, 'No secrets here'),
(3, 'Keep this secret safe');

\set start_pos 9

SELECT id, substring(comment from :start_pos for 10) AS snippet FROM feedback;

-- Write a SELECT query using overlay to replace 'secret' with '******' in comment
-- Your code here

Need a hint?

Use overlay(comment placing '******' from position('secret' in comment) for 6) in the SELECT clause.