PostgreSQL: How to Remove Special Characters from String

Often in PostgreSQL you may want to remove all special characters from a string field.

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

SELECT *,
       regexp_replace(badge, '[^\w]+','','g') AS new_badge
FROM athletes;

This particular example will remove all special characters from the string field named badge in the emp table and save the results to a new field name new_badge.

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

Example: How to Remove Special Characters from String in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company:

-- create table named 'athletes'
CREATE TABLE emp (
  empID INT,
  badge TEXT,
  sales INT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, 'AA005$', 134);
INSERT INTO emp VALUES (0002, 'B#HRUU8#', 201);
INSERT INTO emp VALUES (0003, 'AHH$*JJ', 155);
INSERT INTO emp VALUES (0004, 'B600?33', 98);
INSERT INTO emp VALUES (0005, 'A65!8TE', 65);

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

 empid |  badge   | sales 
-------+----------+-------
     1 | AA005$   |   134
     2 | B#HRUU8# |   201
     3 | AHH$*JJ  |   155
     4 | B600?33  |    98
     5 | A65!8TE  |    65

The badge column displays the badge number for each employee.

We can use the following syntax to remove the special characters from each string in the badge column:

SELECT *,
       regexp_replace(badge, '[^\w]+','','g') AS new_badge
FROM athletes;

 empid |  badge   | sales | new_badge 
-------+----------+-------+-----------
     1 | AA005$   |   134 | AA005
     2 | B#HRUU8# |   201 | BHRUU8
     3 | AHH$*JJ  |   155 | AHHJJ
     4 | B600?33  |    98 | B60033
     5 | A65!8TE  |    65 | A658TE

Notice that the resulting table contains a new column named new_badge that contains the string from the badge column with all special characters removed.

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

Additional Resources

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

PostgreSQL: Extract Substring Before Specific Character
PostgreSQL: Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters