Postgres regex stop creating double space

Question

I have a query that looks like this:

select regexp_replace('john (junior) jones','\([^)]*\)','','g');
  regexp_replace  
------------------
 john  jones

As you can see, this query removes the values in brackets but it results in a double space remaining.

Is there an easy way around this?

So far I have this, which works to an extent:

select regexp_replace((regexp_replace('john (junior) jones','\([^)]*\)','','g')),'\s','');
  regexp_replace  
------------------
 john jones

The above works but not when I pass through something like this:

select regexp_replace((regexp_replace('john (junior) jones (hughes) smith','\([^)]*\)','','g')),'\s','');
   regexp_replace    
---------------------
 john jones  smith

Laurenz Albe · Accepted Answer · 2021-09-01 17:10:11Z

1

SELECT regexp_replace(
          'john (junior) jones (hughes) smith',
          ' *\([^)]*\) *',
          ' ',
          'g'
       );

  regexp_replace  
══════════════════
 john jones smith
(1 row)

To explain the regular expression:

an arbitrary number of spaces, followed by an opening parenthesis ( *\()
an arbitrary number of characters that are not a closing parenthesis ([^)]*)
a closing parenthesis and arbitrarily many spaces (\) *)

That is replaced with a single space.

answered Sep 1, 2021 at 17:10

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

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

2 Comments

nimgwfc Over a year ago

Thanks! That is much closer to what I want - the only issue is if the brackets are at the start/end I am left with a trailing/leading space. Is it best to just wrap this up in a trim as a result?

Laurenz Albe Over a year ago

Yes, perhaps that is a simpler solution than coming up with a more complicated expression.

Collectives™ on Stack Overflow

Postgres regex stop creating double space

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related