How to Replace Strings in PostgreSQL

1. Replace Strings Using the replace() Function in PostgreSQL
2. Replace Strings Using the regexp_replace() Function in PostgreSQL
3. More PostgreSQL replace() Function Examples

This tutorial discusses how to replace strings using PostgreSQL replace() function.

Replace Strings Using the replace() Function in PostgreSQL

PostgreSQL replace() function has the following parameters that are all of the type text:

replace (string text, from text, to text)

The string parameter is the source text on which the replace() function is performed. The from parameter is a substring of the string argument and represents the part to be changed.

The to parameter represents the substring of the string argument to which the from argument is to be changed.

For example, let’s change the word Catfish to Turtle using the replace() function, as shown here:

SELECT replace ('Catfish', 'Catfish', 'Turtle');

The result is:

 replace
---------
 Turtle

Let’s replace the substring Cat in Catfish with Jelly, as shown here:

SELECT replace('Catfish', 'Cat', 'Jelly');

The result is:

  replace
-----------
 Jellyfish

This function can also be used to replace special characters in a string. For example, replacing a blank space with a comma, as shown here:

SELECT replace('A B C D E', ' ', ',');

The result is:

  replace
-----------
 A,B,C,D,E

Let’s say we have a text of random characters, and we want to replace every occurrence of the character a with b regardless of if the character a is uppercase or lowercase. If we run this command, as shown here:

SELECT replace('agAdavA', 'a', 'b');

The result is:

 replace
---------
 bgAdbvA

The result shown above doesn’t satisfy the requirement of replacing all occurrences of both uppercase and lowercase a to b as only the occurrences of lowercase a were replaced. Hence, we would have to introduce the PostgreSQL regexp_replace() function.

Replace Strings Using the regexp_replace() Function in PostgreSQL

PostgreSQL regexp_replace() function has the following parameters that are all of the type text:

regexp_replace (string text, pattern text, replacement text [,flags text])

The string argument is the source string on which the replace function is performed. The pattern parameter represents the regular expression for which there must be a match before a substring of the string argument can be replaced.

The replacement parameter represents the text we are changing the substring of the string argument to. The flags parameter represents the text that can be used to change the behavior of the regexp_replace() function.

In the previous example, where we needed to change all uppercase and lower occurrences of a to b, we can use the regexp_replace() function, as shown here:

SELECT regexp_replace('agAdavA', '[a | A]+', 'b', 'g');

The result is:

 regexp_replace
----------------
 bgbdbvb

With the introduction of the regular expression, all occurrences of uppercase and lowercase a were replaced as required. The g flag ensures that all occurrences and not just the first occurrence of a are replaced.

Here’s the PostgreSQL documentation on pattern matching.

More PostgreSQL replace() Function Examples

Let’s say we have a table with a column named text made up of single words, as shown here:

And we want to replace every occurrence of the word Red with Yellow. We can use the replace() function, as shown here:

UPDATE demo SET text = replace(text, 'Red', 'Yellow');

The result is:

 id |  text
----+--------
  1 | Yellow
  2 | Green
  3 | Blue
  4 | Yellow
  5 | Yellow

Let’s say, in the same table, we have words with special characters in the text field, as shown here:

And we want to replace all occurrences of the hyphen (-) to underscore (_). The replace() function can achieve that, as shown here:

UPDATE demo SET text = replace(text, '-', '_');

The result is:

 id |    text
----+-------------
  6 | g_r_e_e_n
  7 | 1_23__4
  8 | one_and_two
  9 | ___n___2___
 10 | _____

If we insert more records to the table such that the text field has sentences instead of a single word, as shown here:

And we want to replace the word red with yellow. This can be achieved using the replace() function, as shown here:

UPDATE demo SET text = replace(text, 'red', 'yellow');

The result is:

 id |                     text
----+----------------------------------------------
 11 | She bought a yellow bag
 12 | Green is a good color
 13 | The sky is blue
 14 | The color of the shirt is yellow
 15 | They plan to go with blue or yellow balloons

To follow along, here are the commands to run:

--CREATE statement
CREATE TABLE demo (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  text TEXT NOT NULL
);

--insert first set of records
INSERT INTO demo (text)
VALUES
  ('Red'),
  ('Green'),
  ('Blue'),
  ('Red'),
  ('Red');

--insert second set of records
INSERT INTO demo (text)
VALUES
  ('g-r-e-e-n'),
  ('1-23--4'),
  ('one-and-two'),
  ('---n---2---'),
  ('-----');

--insert third and final set of records
INSERT INTO demo (text)
VALUES
  ('She bought a red bag'),
  ('Green is a good color'),
  ('The sky is blue'),
  ('The color of the shirt is red'),
  ('They plan to go with blue or red balloons');

--update statements that include the REPLACE function
UPDATE demo SET text = replace(text, 'Red', 'Yellow');
UPDATE demo SET text = replace(text, '-', '_');
UPDATE demo SET text = replace(text, 'red', 'yellow');

--view all the changes
SELECT * FROM demo;

In this tutorial, we have discussed how to replace strings using PostgreSQL replace() and regexp_replace functions and how the replace() function can be used to update strings in a table.