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

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

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

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

Hint

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

Practice

1. What does the PostgreSQL substring function do when given a string and a start position?

easy

A. Returns the length of the string

B. Extracts part of the string starting from the given position to the end

C. Replaces part of the string starting from the given position

D. Converts the string to uppercase

Solution

1. Step 1: Understand substring function purpose

   The substring function extracts a portion of a string starting at a specified position.

2. Step 2: Analyze given parameters

   When only a start position is given, substring returns from that position to the end of the string.

3. Final Answer:

   Extracts part of the string starting from the given position to the end -> Option B

4. Quick Check:

   substring extracts substring from position [OK]

Hint: Substring cuts string from start position to end if length missing [OK]

Common Mistakes:

• Confusing substring with overlay
• Thinking substring replaces text
• Assuming substring returns string length

2. Which of the following is the correct syntax to replace part of a string using the overlay function in PostgreSQL?

easy

A. overlay(string placing new_substring from start for length)

B. overlay(string from start for length placing new_substring)

C. overlay(string for length from start placing new_substring)

D. overlay(new_substring placing string from start for length)

Solution

1. Step 1: Recall overlay syntax

   The correct syntax is overlay(string placing new_substring from start for length).

2. Step 2: Match options to syntax

   overlay(string placing new_substring from start for length) matches the correct order: string placing new_substring from start for length.

3. Final Answer:

   overlay(string placing new_substring from start for length) -> Option A

4. Quick Check:

   Overlay syntax = string placing new_substring from start for length [OK]

Hint: Remember: overlay(string placing new from start for length) [OK]

Common Mistakes:

• Mixing order of 'from' and 'placing'
• Omitting 'for length' keyword
• Swapping string and new_substring positions

3. What is the result of this query?
SELECT overlay('abcdef' placing 'XYZ' from 2 for 3);

medium

A. 'abXYZf'

B. 'aXYZdef'

C. 'XYZdef'

D. 'aXYZef'

Solution

1. Step 1: Understand overlay parameters

   We replace 3 characters starting at position 2 in 'abcdef' with 'XYZ'. Positions start at 1.

2. Step 2: Apply replacement

   Characters at positions 2,3,4 ('b','c','d') are replaced by 'XYZ', resulting in 'aXYZef'.

3. Final Answer:

   'aXYZef' -> Option D

4. Quick Check:

   Overlay replaces substring from pos 2 length 3 with 'XYZ' [OK]

Hint: Overlay replaces exact length starting at position [OK]

Common Mistakes:

• Replacing wrong substring length
• Starting replacement at wrong position
• Appending instead of replacing

4. Identify the error in this query:
SELECT overlay('hello' placing 'y' for 2 from 3);

medium

A. String literals not quoted

B. Missing length parameter

C. Incorrect order of 'for' and 'from' keywords

D. Overlay function does not exist

Solution

1. Step 1: Check overlay syntax order

   The correct syntax is overlay(string placing new_substring from start for length). The query uses 'placing' then 'for' then 'from', which is wrong order.

2. Step 2: Identify error cause

   The keywords 'for' and 'from' are swapped, causing syntax error.

3. Final Answer:

   Incorrect order of 'for' and 'from' keywords -> Option C

4. Quick Check:

   Overlay syntax order matters: from then for then placing [OK]

Hint: Overlay syntax: from before for before placing [OK]

Common Mistakes:

• Swapping 'for' and 'from' keywords
• Omitting quotes around strings
• Using overlay without required keywords

5. Given the string 'PostgreSQL', which query correctly replaces 'gre' with 'XYZ' using overlay and then extracts the substring starting at position 5 with length 4?

hard

A. SELECT substring(overlay('PostgreSQL' placing 'XYZ' from 5 for 3) from 5 for 4);

B. SELECT substring(overlay('PostgreSQL' from 5 for 3 placing 'XYZ') from 5 for 4);

C. SELECT overlay(substring('PostgreSQL' from 5 for 3) placing 'XYZ' from 5 for 4);

D. SELECT overlay('PostgreSQL' from 5 for 4 placing 'XYZ');

Solution

1. Step 1: Replace 'gre' with 'XYZ' using overlay

   'gre' starts at position 5 and has length 3. Correct overlay syntax is overlay(string placing new_substring from start for length). SELECT substring(overlay('PostgreSQL' placing 'XYZ' from 5 for 3) from 5 for 4); matches this.

2. Step 2: Extract substring from position 5 length 4

   Apply substring on the result starting at position 5 for length 4. SELECT substring(overlay('PostgreSQL' placing 'XYZ' from 5 for 3) from 5 for 4); applies substring correctly after overlay.

3. Final Answer:

   SELECT substring(overlay('PostgreSQL' placing 'XYZ' from 5 for 3) from 5 for 4); -> Option A

4. Quick Check:

   Overlay then substring with correct syntax = SELECT substring(overlay('PostgreSQL' placing 'XYZ' from 5 for 3) from 5 for 4); [OK]

Hint: Overlay syntax: placing after string, then substring after overlay [OK]

Common Mistakes:

• Mixing overlay keyword order
• Applying substring before overlay
• Wrong positions or lengths in functions