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

> The REPLACE() is a very convenient function that is used to search and replace all the appearances of a string with a new substring/text.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-replace-a-string-using-replace-function-in-postgresql/)