Introduction to PostgreSQL REPLACE() function

TheREPLACE() function replaces all occurrences of a substring with a new one in a string.

Here’s the syntax of the PostgreSQLREPLACE() function:

REPLACE(source, from_text, to_text);

TheREPLACE() function accepts three arguments:

• source: This is an input string that you want to replace.
• from_text: This is the substring that you want to search and replace. If thefrom_text appears multiple times in thesource string, the function will replace all the occurrences.
• to_text: This is the new substring that you want to replace thefrom_text.

PostgreSQL REPLACE() function examples

Let’s explore some examples of using theREPLACE() function.

1) Basic PostgreSQL REPLACE() function example

The following example uses theREPLACE() function to replace the string'A' in the string'ABC AA' with the string'Z':

SELECT REPLACE ('ABC AA','A','Z');

Output:

replace--------- ZBC ZZ(1 row)

In this example, theREPLACE() function replaces all the characters'A' with the character'Z' in a string.

2) Using the PostgreSQL REPLACE() function with table data

If you want to search and replace a substring in a table column, you use the following syntax:

UPDATE  table_nameSET  column_name = REPLACE(column, old_text, new_text)WHERE  condition;

Let’s see the following example.

First,create a new table calledposts that has three columnsid,title, andurl:

CREATE TABLE posts(    idSERIAL PRIMARY KEY,    titleVARCHAR(255)NOT NULL,    url VARCHAR(255)NOT NULL);INSERT INTO posts(title,url)VALUES('PostgreSQL Tutorial','http://neon.tech/postgresql'),('PL/pgSQL','http://neon.tech/postgresql/postgresql-plpgsql/'),('PostgreSQL Administration','http://neon.tech/postgresql/postgresql-administration/')RETURNING*;

Output:

id |           title           |                             url----+---------------------------+--------------------------------------------------------------  1 | PostgreSQL Tutorial       | http://neon.tech/postgresql  2 | PL/pgSQL                  | http://neon.tech/postgresql/postgresql-plpgsql/  3 | PostgreSQL Administration+| http://neon.tech/postgresql/postgresql-administration/    |                           |(3 rows)INSERT 0 3

Second, replace thehttp in theurl column with thehttps using theREPLACE() function:

UPDATE postsSET url = REPLACE(url,'http','https');

Output:

UPDATE 3

The output indicates that three rows were updated.

Third, verify the update by retrieving data from thecustomer table:

SELECT * FROM posts;

Output:

id |           title           |                              url----+---------------------------+---------------------------------------------------------------  1 | PostgreSQL Tutorial       | https://neon.com/postgresql  2 | PL/pgSQL                  | https://neon.com/postgresql/postgresql-plpgsql/  3 | PostgreSQL Administration+| https://neon.com/postgresql/postgresql-administration/    |                           |(3 rows)

The output indicates that thehttp in theurl column were replaced by thehttps.

Summary

• Use the PostgreSQLREPLACE() function to replace all occurrences of a substring in a string with another a new substring.

Last updated onJanuary 29, 2024

On this page

• Introduction to PostgreSQL REPLACE() function
• PostgreSQL REPLACE() function examples
• Summary