PostgreSQL regexp_replace() to keep just one whitespace

Question

I need to clean up a string column with both whitespaces and tabs included within, at the beginning or at the end of strings (it's a mess !). I want to keep just one whitespace between each word. Say we have the following string that includes every possible situation :

mystring = '  one two    three      four    '

2 whitespaces before 'one'
1 whitespace between 'one' and 'two'
4 whitespaces between 'two' and 'three'
2 tabs after 'three'
1 tab after 'four'

Here is the way I do it :

I delete leading and trailing whitespaces
I delete leading and trailing tabs
I replace both 'whitespaces repeated at least two' and tabs by a sole whitespace

WITH

  t1 AS (SELECT'  one two    three      four    '::TEXT AS mystring),

  t2 AS (SELECT TRIM(both ' ' from mystring) AS mystring FROM t1),

  t3 AS (SELECT TRIM(both '\t' from mystring) AS mystring FROM t2)

  SELECT regexp_replace(mystring, '(( ){2,}|\t+)', ' ', 'g') FROM t3 ;

I eventually get the following string, which looks nice but I still have a trailing whitespace...

'one two three four '

Any idea on doing it in a more simple way and solving this last issue ?

Many thanks !

Any help ? Someone posted a comment yesterday and deleted it... I've had no time to look at it. Thanks ! — wiltomap
– wiltomap, Commented Sep 19, 2014 at 6:13

Braiam · Accepted Answer · 2022-08-25 17:40:06Z

87

SELECT trim(regexp_replace(col_name, '\s+', ' ', 'g')) as col_name FROM table_name;

Or In case of update :

UPDATE table_name SET col_name = trim(regexp_replace(col_name, '\s+', ' ', 'g'));

The regexp_replace is flags are described on this section of the documentation.

edited Aug 25, 2022 at 17:40

Braiam

4,49511 gold badges50 silver badges83 bronze badges

answered Nov 14, 2014 at 1:50

augustorf

8867 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yarin Over a year ago

On Postgres 9.5 use \s instead of \\s.

Richard Greenwood Over a year ago

On Postgres 9.4 and 10.0 it also seems that \s should be used rather than \\s.

bsplosion Aug 26 at 20:42

What had tripped me up is that regexp_replace does not do a global replace by default and you must issue the 'g' switch as the fourth parameter for it to behave in that way. It looked like the operation was having no effect since my string samples had single spaces initially followed later in the string with multiple spaces.

openwonk · Accepted Answer · 2018-04-29 05:36:30Z

3

SELECT trim(regexp_replace(mystring, '\s+', ' ', 'g')) as mystring FROM t1;

Posting an answer in case folks don't look at comments.

Use '\s+'

Not '\\s+'

Worked for me.

answered Apr 29, 2018 at 5:36

openwonk

15.8k7 gold badges50 silver badges41 bronze badges

Comments

Dan · Accepted Answer · 2018-05-07 21:47:04Z

It didn't work for me with trim and regexp_replace. So I came with another solution:

SELECT trim(
    array_to_string(
        regexp_split_to_array('  test    with many  spaces  for        this   test  ', E'\\s+')
    , ' ')
) as mystring;

First regexp_split_to_array eliminates all spaces leaving "blanks" at the beginning and the end.

-- regexp_split_to_array output:
-- {"",test,with,many,spaces,for,this,test,""}

When using array_to_string all the ',' become spaces

-- regexp_split_to_array output ( '_' instead of spaces for viewing ):
-- _test_with_many_spaces_for_this_test_

The trim is to remove the head and tail

-- trim output ( '_' instead of spaces for viewing ):
-- test_with_many_spaces_for_this_test

Collectives™ on Stack Overflow

PostgreSQL regexp_replace() to keep just one whitespace

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related