PostgreSQL REPLACE Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL REPLACE function to clean data in your database.

Introduction to the PostgreSQL REPLACE function #

The REPLACE function takes a string and replaces all occurrences of a substring with a new one.

Here’s the syntax of the REPLACE function:

REPLACE(string, substring, new_substring)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The REPLACE function takes three parameters:

• string is the input string where all replacements will occur.
• substring is the substring in the input string you want to replace.
• new_substring is the substring that will replace the substring.

The REPLACE function returns a new string with all occurrences of a substring in the input string replaced by a new one.

The REPLACE() function returns NULL if any argument is NULL.

Basic PostgreSQL REPLACE function example #

The following example uses the REPLACE function to replace all occurrences of "We" by "PostgreSQL" in the string "We will, We will rock you":

SELECT
  REPLACE('We will, We will rock you.', 'We', 'PostgreSQL') new_string;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                 new_string
--------------------------------------------
 PostgreSQL will, PostgreSQL will rock you.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL REPLACE function to transform data #

The following SELECT statement uses the REPLACE function to retrieve the names of products with the brand id 2 (Apple) but with the word "Apple" replaced by an empty string (""):

SELECT
  product_name,
  REPLACE(product_name, 'Apple', '') new_product_name
FROM
  products
WHERE
  brand_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name       |  new_product_name
-------------------------+--------------------
 Apple iPhone 15         |  iPhone 15
 Apple iPhone 15 Pro Max |  iPhone 15 Pro Max
 Apple iPad Pro 12.9     |  iPad Pro 12.9
 Apple AirPods Pro 3     |  AirPods Pro 3
 Apple Watch Series 9    |  Watch Series 9
 Apple iMac 24"          |  iMac 24"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the REPLACE function transforms the product names when retrieving them. It does not modify the data in the products table.

Replacing texts in a table #

The following example uses the REPLACE function in the UPDATE statement to update the product names:

UPDATE products
SET
  product_name = REPLACE(product_name, 'Apple', '')
WHERE
  brand_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This query pattern helps clean up data in the database:

UPDATE table_name
SET
  column_name = REPLACE(column_name, 'substring', 'new_substring')
WHERE
  condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Common mistake when using the REPLACE function #

It’s important to note that the column_name in the REPLACE function does not have a single quote (').

If you place the column name inside the quote, you’ll set all the values in the column_name to the same unexpected value. For example:

UPDATE products
SET
  product_name = REPLACE('product_name', 'Apple', '')
WHERE
  brand_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

First, the REPLACE function will return the following string:

product_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, the UPDATE statement will update the product name of the products with the brand_id 2 to 'product_name'.

Summary #

• Use the REPLACE function to return a new string by replacing all occurrences of a substring with a new one.