Often in PostgreSQL you may want to remove a substring from a string.
You can use the replace() function with the following syntax to do so:
SELECT *,
replace(team, 'are ', '') AS new_team
FROM athletes;
This particular example will create a new column named new_team that removes the substring ‘are ‘ from the existing string in the team column of the athletes table.
Note that the replace() function is used to replace each occurrence of a substring in a string field.
By specifying no replacement pattern, we are able to simply remove each occurrence of a particular substring from a field.
The following example shows how to use this syntax in practice.
Example: How to Remove Substring from String in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball teams:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'The Mavs are from Dallas');
INSERT INTO athletes VALUES (0002, 'The Nets are from Brooklyn');
INSERT INTO athletes VALUES (0003, 'The Rockets are from Houston');
INSERT INTO athletes VALUES (0004, 'The Kings are from Sacramento');
INSERT INTO athletes VALUES (0005, 'The Celtics are from Boston');
-- view all rows in table
SELECT * FROM athletes;
id | team
----+-------------------------------
1 | The Mavs are from Dallas
2 | The Nets are from Brooklyn
3 | The Rockets are from Houston
4 | The Kings are from Sacramento
5 | The Celtics are from Boston
Suppose that we would like to remove the substring ‘are ‘ from each string in the team column.
We can use the following syntax to do so:
SELECT *,
replace(team, 'are ', '') AS new_team
FROM athletes;
id | team | replace
----+-------------------------------+---------------------------
1 | The Mavs are from Dallas | The Mavs from Dallas
2 | The Nets are from Brooklyn | The Nets from Brooklyn
3 | The Rockets are from Houston | The Rockets from Houston
4 | The Kings are from Sacramento | The Kings from Sacramento
5 | The Celtics are from Boston | The Celtics from Boston
Notice that the resulting table contains a new column named new_team that contains the string from the team column with the substring ‘are ‘ removed.
Feel free to use similar syntax to remove a substring from a string in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Calculate Difference Between Rows
