CODEWITHSUNDEEP
regexstringpostgresqlreplacewhitespace
wiltomap
wiltomap

Reputation: 4233

PostgreSQL regexp_replace() to keep just one whitespace

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    '

Here is the way I do it :

  1. I delete leading and trailing whitespaces
  2. I delete leading and trailing tabs
  3. 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 !

Upvotes: 39

Views: 46324

Answers (3)

augustorf
augustorf

Reputation: 886

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.

Upvotes: 87

Dan
Dan

Reputation: 1881

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

Upvotes: 0

openwonk
openwonk

Reputation: 15567

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.

Upvotes: 3

Related Questions