PostgreSQL: How to Remove Specific Characters from String

Often in PostgreSQL you may want to remove several specific characters from a string.

You can use the translate() function with the following syntax to do so:

SELECT *,
       translate(team, 'acs', '') AS new_team
FROM athletes;

This particular example will create a new column named new_team that removes the following characters from the existing string in the team column of the athletes table:

• a
• c
• s

Note that the translate() function is used to replace a set of single characters with a new pattern.

By specifying no replacement pattern, we are able to simply remove each occurrence of the characters that we specified.

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

Example: How to Remove Specific Characters 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 (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

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

 athleteid |  team   | points 
-----------+---------+--------
         1 | Celtics |     22
         2 | Hawks   |     30
         3 | Kings   |     12
         4 | Kings   |     25
         5 | Mavs    |     31
         6 | Mavs    |     16
         7 | Hawks   |     10
         8 | Celtics |     14
         9 | Hawks   |     20
        10 | Hawks   |     26

Suppose that we would like to remove the following characters from each string in the team column:

• a
• c
• s

We can use the following syntax to do so:

SELECT *,
       translate(team, 'acs', '') AS new_team
FROM athletes;

 athleteid |  team   | points | new_team 
-----------+---------+--------+----------
         1 | Celtics |     22 | Celti
         2 | Hawks   |     30 | Hwk
         3 | Kings   |     12 | King
         4 | Kings   |     25 | King
         5 | Mavs    |     31 | Mv
         6 | Mavs    |     16 | Mv
         7 | Hawks   |     10 | Hwk
         8 | Celtics |     14 | Celti
         9 | Hawks   |     20 | Hwk
        10 | Hawks   |     26 | Hwk

Notice that the resulting table contains a new column named new_team that contains the string from the team column with the characters ‘a’, ‘c’ and ‘s’ removed.

Feel free to use similar syntax to remove specific characters from a string in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: Find Last Occurrence of Character in String
PostgreSQL: How to Remove Special Characters from String
PostgreSQL: Count Number of Occurrences of Character in String