How to use Postgres replace

Postgres replace function is used to replace a substring of a text with another substring.

The syntax for using the PostgreSQL REPLACE function is:

REPLACE(old_text, old_substring, new_substring)

For example, we have a table email template for preparing email detail for multiple clients.

id|subject |detail                              |
--+--------+------------------------------------+
 1|GREETING|Hello [NAME], Thank you for reading.|

SQL for creating the table

CREATE TABLE public.email_template (
 id serial NOT NULL,
 subject varchar NOT NULL,
 detail text NOT NULL
);

We want to replace [NAME] in detail with John. So, we use replace function.

SELECT REPLACE(detail, '[NAME]', 'John') AS detail 
FROM email_template
WHERE id = 1

The result

detail                            |
----------------------------------+
Hello John, Thank you for reading.|

Next, we also want to change [NAME] to John and text ‘reading’ to ‘watching’ so we can wrap replace function with another replace function

SELECT REPLACE(REPLACE(detail, '[NAME]', 'John'), 'reading', 'watching') AS detail 
FROM email_template 
WHERE id = 1

The result

detail                             |
-----------------------------------+
Hello John, Thank you for watching.|

After that, we realize that we not only want to change the text ‘reading’ to ‘watching’ but we want to replace it with so many actions. So, we’ll update the text ‘reading’ to ‘[ACTION]’ in the database

UPDATE email_template 
SET detail = REPLACE(detail, 'reading', '[ACTION]')
WHERE id = 1

The result

id|subject |detail                               |
--+--------+-------------------------------------+
 1|GREETING|Hello [NAME], Thank you for [ACTION].|

Thank you for reading & happy coding :)

Let’s checkout my recommended book: Mastering PostgreSQL 13: Build, administer, and maintain database applications efficiently with PostgreSQL 13, 4th Edition