PostgreSQL – REPLACE Function
In PostgreSQL, the REPLACE function is used to search and replace all occurrences of a string with a new one.
Syntax: REPLACE(source, old_text, new_text );
Let’s analyze the above syntax:
- The source is a string where you want to replace the existing string.
- The old_text is the string that is to be searched and subsequently replaced. The old_text can occur multiple times and each of them gets replaced on the function is executed.
- The new_text is the new text string that is suppose to replace the old text ( old_text ).
Example 1:
The following statement replaces the substring ‘tt’ with ‘xx’ in a URL:
SELECT REPLACE ( 'https://www.w3wiki.net', 'tt', 'xx' );
Output:
Example 2:
The below statement updates the email column to replace the domain ‘sakilacustomer.org’ with ‘Beginnerforgeek.org’, in the customer table of the sample database, ie, dvdrental:
UPDATE customer SET email = REPLACE ( email, 'sakilacustomer.org', 'w3wiki.net' );
To verify it use the below statement:
SELECT first_name, last_name, email FROM customer;
Output: