Often in PostgreSQL you may want to replace multiple strings in a column.
You can use a nested REPLACE statement with the following syntax to do so:
SELECT athleteID,
team,
REPLACE(REPLACE(team, 'Mavs', 'Mavericks'), 'Cavs', 'Cavaliers')
FROM athletes;
This particular example will make the following replacements in the team column of the table named athletes:
- Replace Mavs with Mavericks
- Replace Cavs with Cavaliers
The following example shows how to use this syntax in practice.
Example: How to Replace Multiple Strings in a Column 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,
player_info TEXT.
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Mavs', 30, 12);
INSERT INTO athletes VALUES (0003, 'Cavs', 12, 3);
INSERT INTO athletes VALUES (0004, 'Cavs', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Cavs', 10, 5);
INSERT INTO athletes VALUES (0008, 'Mavs', 14, 13);
INSERT INTO athletes VALUES (0009, 'Mavs', 20, 8);
INSERT INTO athletes VALUES (0010, 'Cavs', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+------+--------+---------
1 | Mavs | 22 | 5
2 | Mavs | 30 | 12
3 | Cavs | 12 | 3
4 | Cavs | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Cavs | 10 | 5
8 | Mavs | 14 | 13
9 | Mavs | 20 | 8
10 | Cavs | 26 | 3
Suppose that we would like to replace the following strings in the team column of the table:
- Replace Mavs with Mavericks
- Replace Cavs with Cavaliers
We can use the following syntax to do so:
SELECT athleteID,
team,
REPLACE(REPLACE(team, 'Mavs', 'Mavericks'), 'Cavs', 'Cavaliers')
FROM athletes;
athleteid | team | replace
-----------+------+-----------
1 | Mavs | Mavericks
2 | Mavs | Mavericks
3 | Cavs | Cavaliers
4 | Cavs | Cavaliers
5 | Mavs | Mavericks
6 | Mavs | Mavericks
7 | Cavs | Cavaliers
8 | Mavs | Mavericks
9 | Mavs | Mavericks
10 | Cavs | Cavaliers
Notice that the new column named replace has replaced the strings Mavs and Cavs with Mavericks and Cavaliers, respectively.
Note: In this example we used two nested REPLACE statements to replace two specific strings in a column but you can use as many REPLACE statements as you’d like to replace even more strings in a column.
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 After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
