Replacing a record like an email, address, phone number, etc., is a very common task. In PostgreSQL, the REPLACE() function finds a string/substring and replaces it with a new string/substring. The REPLACE() function takes three parameters i.e. an original string, a substring that you want to replace, and a new substring that will replace the old substring.

The aim of this post is to explain the usage of REPLACE() function with the help of examples. So, let’s get started!

How To Replace a String Using REPLACE() Function in PostgreSQL?

The REPLACE() is a very convenient function to search and replace all the appearances of a string with a new substring/text. The basic syntax of the REPLACE() function will be as follows:

REPLACE(origianl_string, old_substring, new_substring );

From the above snippet, you can observe that the REPLACE() function accepts three parameters. All three parameters are self-explanatory i.e. it takes a string, an old_substring that needs to be replaced, and a new_substring that will replace the old_substring.

Example#1: Basic Usage of the Postgres REPLACE() Function

This example will give you a basic idea of the REPLACE() function:

SELECT REPLACE ('johnjacobs@gmail.com', 'j', 'J');

In this example, we replaced all the occurrences of the small “j” with the capital “J”

Example #2: How to Replace a Substring With a new Substring Using the REPLACE() Function

Let's replace the “com” with the “org”:

SELECT REPLACE ('johnjacobs.com', 'com', 'org');

The output shows that the REPLACE() method successfully replaced the “com” with “org”.

How to Replace Text/String in a Table’s Column Using the REPLACE() Function

Suppose we need to replace a substring within the table’s column. To do that, you need to follow the below syntax:

UPDATE tab_name
SET col_name = REPLACE(col_name, old_string, new_string)
WHERE condition;

In this syntax, tab_name, and col_name represents the name of the targeted table and column, respectively. REPLACE() is a function, old_string represents a string that needs to be replaced, while the new_string is a string that will replace the old_string.

Example #1: How to Use REPLACE() Function to Replace a Substring Within the Table’s Column?

We have a table named bike_details in our database whose details are as follows:

SELECT * FROM bike_details;

Suppose we have to update the bike_color column i.e. we need to replace the Red color with White. To do that, let’s execute the below query:

UPDATE bike_details
SET bike_color = REPLACE(bike_color, 'Red', 'White');

Let’s execute the SELECT statement to see the updated table.

SELECT * FROM bike_details;

The output clarified that in the bike_details table, all the occurrences of the “Red” substring had been replaced with the “White” substring.

Example #2: How to Use REPLACE() Function to Replace Specific Occurrences of a Substring Within the Table’s Column?

You can use the WHERE Clause to replace only specific occurrences as we did in the below-given example:

UPDATE bike_details
SET bike_color = REPLACE(bike_color, 'Blue', 'Red')
WHERE bike_id = 8;

Let’s run the below-given query to verify the replaced/updated values of the bike_details table:

SELECT * FROM bike_details;

The output proves that this time the REPLACE() function replaces only one substring whose id is equal to 8.

Conclusion

The REPLACE() is a very convenient function that searches for the desired string and replaces all the appearances of that string with a new string/text. The REPLACE() function takes three parameters i.e. an original string, a substring that you want to replace, and a new substring that will replace the old substring. This write-up explained how to use the REPLACE() function in PostgreSQL with the help of examples.