PostgreSQL: How to Replace Multiple Spaces with One Space

Often in PostgreSQL you may want to replace multiple spaces in a string with just one space.

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

SELECT *,
       trim(regexp_replace(team, '\s+', ' ', 'g')) AS new_team
FROM athletes;

This particular example replaces all occurrences of multiple spaces with a single space in the team column of the athletes table and saves the results to a new column named new_team.

Note that we use the regexp_replace() function with the following arguments:

• ‘\s+’: Match patterns with multiple spaces
• ‘ ‘: Use a single space as a replacement
• ‘g’: Make this replacement “globally”, i.e. for all occurrences

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

Example: How to Replace Multiple Spaces with One Space 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 (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Dallas  Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Atlanta   Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Boston Celtics', 15);
INSERT INTO athletes VALUES (0004, 'Denver Nuggets', 20);
INSERT INTO athletes VALUES (0005, 'Brooklyn    Nets', 26);
INSERT INTO athletes VALUES (0006, 'Miami    Heat', 42);

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

 athleteid |       team       | points 
-----------+------------------+--------
         1 | Dallas  Mavs     |     22
         2 | Atlanta   Hawks  |     30
         3 | Boston Celtics   |     15
         4 | Denver Nuggets   |     20
         5 | Brooklyn    Nets |     26
         6 | Miami    Heat    |     42

Suppose that we would like to replace all occurrences of multiple spaces with one space in each string in the team column.

We can use the following syntax to do so:

SELECT *,
       trim(regexp_replace(team, '\s+', ' ', 'g')) AS new_team
FROM athletes;

 athleteid |       team       | points |    new_team    
-----------+------------------+--------+----------------
         1 | Dallas  Mavs     |     22 | Dallas Mavs
         2 | Atlanta   Hawks  |     30 | Atlanta Hawks
         3 | Boston Celtics   |     15 | Boston Celtics
         4 | Denver Nuggets   |     20 | Denver Nuggets
         5 | Brooklyn    Nets |     26 | Brooklyn Nets
         6 | Miami    Heat    |     42 | Miami Heat

The new column named new_team has replaced each occurrence of multiple spaces in the team column with a single space instead.

You can use similar syntax to replace multiple spaces with a single space in a column of your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Replace Multiple Strings in a Column
PostgreSQL: How to Replace Null Values with String
PostgreSQL: How to Replace Null Values with Zero
PostgreSQL: How to Replace All Occurrences of String in Text Field