PostgreSQL: Replace All Occurrences of String in Text Field

Often in PostgreSQL you may want to replace all occurrences of a string in a text field.

You can use the following syntax to do so:

SELECT regexp_replace(name, 'Team','','gi') AS new_name
FROM athletes;

This particular example will replace all occurrences of the string ‘team’ with nothing in the text field named name of the table named athletes.

The results will be saved to a new column named new_name.

Note that we use the regex_preplace() function to replace one pattern with another pattern, using the g parameter to specify that this replacement should be made globally, i.e. for each occurrence in the string and the i parameter to specify that the replacement should be case-insensitive.

The following example shows how to use this syntax in practice.

Example: Replace All Occurrences of String in Text Field in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  name TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'TeamMavsTeam', 14);
INSERT INTO athletes VALUES (0002, 'TeamHawksTeam', 22);
INSERT INTO athletes VALUES (0003, 'TeamNets', 30);
INSERT INTO athletes VALUES (0004, 'teamKingsTeam', 18);
INSERT INTO athletes VALUES (0005, 'TeamTeamLakers', 26);
INSERT INTO athletes VALUES (0006, 'TeamCelticsTeam', 27);

-- view all rows in table
SELECT * FROM athletes;

 id |      name       | points 
----+-----------------+--------
  1 | TeamMavsTeam    |     14
  2 | TeamHawksTeam   |     22
  3 | TeamNets        |     30
  4 | teamKingsTeam   |     18
  5 | TeamTeamLakers  |     26
  6 | TeamCelticsTeam |     27

Suppose that we would like to remove each occurrence of the string ‘Team’ (regardless of case) from the name column.

We can use the following syntax to do so:

SELECT *,
       regexp_replace(name, 'Team','','gi') AS new_name
FROM athletes;

 id |      name       | points | new_name 
----+-----------------+--------+----------
  1 | TeamMavsTeam    |     14 | Mavs
  2 | TeamHawksTeam   |     22 | Hawks
  3 | TeamNets        |     30 | Nets
  4 | teamKingsTeam   |     18 | Kings
  5 | TeamTeamLakers  |     26 | Lakers
  6 | TeamCelticsTeam |     27 | Celtics

Notice that the resulting table contains a new column named new_name that contains the string from the original name column with each occurrence of the string ‘Team’ removed.

Feel free to use similar syntax to remove each occurrence of a specific string in a text field of your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Numbers from a String