JIYIK CN >

This article discusses how to use PostgreSQL replace() functions to replace strings.

Use the PostgreSQL replace()function to replace strings

PostgreSQL replace()functions have the following arguments, which are all text types:

replace (string text, from text, to text)

stringThe argument is replace()the source text to execute the function. fromThe argument is stringa substring of the argument that represents the portion to be changed.

toParameter represents the substring of parameter to which parameter fromis to be changed .string

For example, let's use replace()the function to Catfishchange the word to Turtle, as follows:

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

turn out:

 replace
---------
 Turtle

Let's replace Catfishthe substring in with , as follows:CatJelly

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

turn out:

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

This function can also be used to replace special characters in a string. For example, replace spaces with commas as follows:

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

turn out:

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

Suppose we have a text of random characters, and we want to breplace every occurrence of that character with a, regardless of whether the character ais uppercase or lowercase. If we run this command, as shown below:

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

turn out:

 replace
---------
 bgAdbvA

The result shown above does not satisfy the requirement of areplacing all occurrences of uppercase and lowercase with b, since only the occurrences of lowercase are replaced a. Therefore, we have to introduce the PostgreSQL regexp_replace()function.

regexp_replace()Use the function to replace strings in PostgreSQL

PostgreSQL regexp_replace()functions have the following arguments, which are all text types:

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

stringThe argument is the source string to perform the replace function patternon. The argument represents a regular expression that must match before a substring of the string argument can be replaced.

replacementParameter represents the text that we change the substring of the string parameter to. flagsParameter represents regexp_replace()text that can be used to change the behavior of the function.

In the previous example, we need to achange all occurrences of uppercase and lowercase to b, we can use regexp_replace()the function, as follows:

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

turn out:

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

With the introduction of regular expressions, all occurrences of uppercase and lowercase aare replaced as necessary. gThe -e flag ensures that all occurrences are replaced, not just the first a.

Here is the PostgreSQL documentation on pattern matching .

More PostgreSQL replace()Function Examples

Suppose we have a table with a textcolumn called consisting of single words, like this:

We want to Yellowreplace every occurrence of the word with Red. We can use replace()the function as follows:

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

turn out:

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

Suppose in the same table we texthave words with special characters in the field like this:

We want to replace all occurrences of hyphens ( -) with underscores ( _). replace()A function can do this, as shown below:

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

turn out:

 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 into the table so that textthe field contains sentences instead of single words, as shown below:

We want to yellowreplace the word with red. This can be replace()achieved using the function as follows:

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

turn out:

 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

Next, 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 article, we discussed how to replace a string using PostgreSQL replace()and regexp_replacethe replace function, and how to replace()update a string in a table using the replace function.