Replace using PostgreSQL




You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server.


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:

Replace PostgresSQL Sample Data
PizzaDB Sample Data

Download the Sample PizzaDB on GitHub.


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')


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

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SqlServer