PostgreSQL Tutorial: REPLACE Function

August 1, 2023

Summary: In this tutorial, we will introduce you to the PostgreSQL replace functions that search and replace a substring with a new substring in a string.

Table of Contents

Introduction to PostgreSQL REPLACE() function

Sometimes, you want to search and replace a string in a column with a new one such as replacing outdated phone numbers, broken URLs, and spelling mistakes.

To search and replace all occurrences of a string with a new one, you use the REPLACE() function.

The following illustrates the syntax of the PostgreSQL REPLACE() function:

REPLACE(source, old_text, new_text );

The REPLACE() function accepts three arguments:

source is a string where you want to replace.
old_text is the text that you want to search and replace. If the old_text appears multiple times in the string, all of its occurrences will be replaced.
new_text is the new text that will replace the old text ( old_text).

PostgreSQL REPLACE() function examples

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

1) Basic PostgreSQL REPLACE() function example

See the following example of using the REPLACE()function:

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

Output:

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

In this example, we replaced all characters ‘A’ with the character ‘Z’ in a string.

The following example replaces the substring tt with xx in a URL:

SELECT
	REPLACE (
		'https://www.rockdata.net',
		'tt',
		'xx'
	);

Output:

         replace
--------------------------
 hxxps://www.rockdata.net
(1 row)

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_name
SET 
   column_name = REPLACE(column,old_text,new_text)
WHERE 
   condition

Let’s use the customer table in the sample database for the demonstration:

SELECT
	first_name,
	last_name,
	email
FROM
	customer;

Output:

 first_name  |  last_name   |                  email
-------------+--------------+------------------------------------------
 Jared       | Ely          | jared.ely@sakilacustomer.org
 Mary        | Smith        | mary.smith@sakilacustomer.org
 Patricia    | Johnson      | patricia.johnson@sakilacustomer.org
 Linda       | Williams     | linda.williams@sakilacustomer.org
 Barbara     | Jones        | barbara.jones@sakilacustomer.org
 Elizabeth   | Brown        | elizabeth.brown@sakilacustomer.org
 Jennifer    | Davis        | jennifer.davis@sakilacustomer.org
 Maria       | Miller       | maria.miller@sakilacustomer.org
 Susan       | Wilson       | susan.wilson@sakilacustomer.org
 ...

Now, suppose you want to update the email column to replace the domain sakilacustomer.org with rockdata.net, you use the following statement:

UPDATE 
   customer
SET 
   email = REPLACE (
  	email,
	'sakilacustomer.org',
	'rockdata.net'
   );

Because we omitted the WHERE clause, all rows in the customer table were updated.

Let’s verify if the replacements have taken place.

SELECT
	first_name,
	last_name,
	email
FROM
	customer;

Output:

 first_name  |  last_name   |               email
-------------+--------------+------------------------------------
 Jared       | Ely          | jared.ely@rockdata.net
 Mary        | Smith        | mary.smith@rockdata.net
 Patricia    | Johnson      | patricia.johnson@rockdata.net
 Linda       | Williams     | linda.williams@rockdata.net
 Barbara     | Jones        | barbara.jones@rockdata.net
 Elizabeth   | Brown        | elizabeth.brown@rockdata.net
 Jennifer    | Davis        | jennifer.davis@rockdata.net
 Maria       | Miller       | maria.miller@rockdata.net
 Susan       | Wilson       | susan.wilson@rockdata.net
 ...