Let’s explore some examples of using the REPLACE() function.

1) Basic PostgreSQL REPLACE() function example

The following example uses the REPLACE() function to replace the string 'A' in the string 'ABC AA' with the string 'Z':

SELECT REPLACE ('ABC AA', 'A', 'Z');

Output:

replace--------- ZBC ZZ(1 row)

In this example, the REPLACE() function replaces all the characters 'A' with the character 'Z' in a string.

2) Using the PostgreSQL REPLACE() function with table data

If you want to search and replace a substring in a table column, you use the following syntax:

UPDATE  table_nameSET  column_name = REPLACE(column, old_text, new_text)WHERE  condition;

Let’s see the following example.

First, create a new table called posts that has three columns id, title, and url:

CREATE TABLE posts(    id SERIAL PRIMARY KEY,    title VARCHAR(255) NOT NULL,    url VARCHAR(255) NOT NULL);INSERT INTO posts(title, url)VALUES('PostgreSQL Tutorial', 'http://neon.tech/postgresql'),('PL/pgSQL', 'http://neon.tech/postgresql/postgresql-plpgsql/'),('PostgreSQL Administration', 'http://neon.tech/postgresql/postgresql-administration/')RETURNING *;

Output:

id |           title           |                             url----+---------------------------+--------------------------------------------------------------  1 | PostgreSQL Tutorial       | http://neon.tech/postgresql  2 | PL/pgSQL                  | http://neon.tech/postgresql/postgresql-plpgsql/  3 | PostgreSQL Administration+| http://neon.tech/postgresql/postgresql-administration/    |                           |(3 rows)INSERT 0 3

Second, replace the http in the url column with the https using the REPLACE() function:

UPDATE postsSET url = REPLACE(url, 'http','https');

Output:

UPDATE 3

The output indicates that three rows were updated.

Third, verify the update by retrieving data from the customer table:

SELECT * FROM posts;

Output:

id |           title           |                              url----+---------------------------+---------------------------------------------------------------  1 | PostgreSQL Tutorial       | https://neon.com/postgresql  2 | PL/pgSQL                  | https://neon.com/postgresql/postgresql-plpgsql/  3 | PostgreSQL Administration+| https://neon.com/postgresql/postgresql-administration/    |                           |(3 rows)

The output indicates that the http in the url column were replaced by the https.

Summary

• Use the PostgreSQL REPLACE() function to replace all occurrences of a substring in a string with another a new substring.