Problem
You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server.
Background
The owners of the PizzaDB shops are changing their drinks from Coke to Pepsi. Let’s help them see what the changes would be, before they make them permanent.
We’ll write a query show them the new product listing:
Download the Sample PizzaDB on GitHub.
Solution
We’ll do this in two steps. This first is to write a query to test the REPLACE logic. Once we’re certain it is working correctly, we’ll make the changes permanent using the UPDATE statement.
Step 1:
For the first step write the query the REPLACE function.
The syntax to use is
REPLACE(productname, 'Coke', 'Pepsi')
which means to look in the productname column for any occurrences of Coke and replace them with Pepsi.
The following works with MySQL, PostgreSQL, and SQLServer:
SELECT productname OriginalProductList, REPLACE(productname, 'Coke', 'Pepsi') NewProductList FROM Product
Step 2:
Once you’re certain of the result use the update statement to make the change permanent.
UPDATE Product SET productname = REPLACE(productname, 'Coke', 'Pepsi')
Discussion
The REPLACE function is available for MySQL, PostgreSQL, and SQL Server. Keep in mind PostgreSQL is case sensitive.
If you need to do case insensitive replacements with PostgreSQL then look into REGEXP_REPLACE. This replace look for variations of coke and replaces them with Pepsi:
REGEXP_REPLACE(productname, 'coke', 'Pepsi','i')
Leave a Reply