Postgresql: Find and replace multiple characters within parentheses in string

Question

I have a column called SKETCH that contains vector strings varying in length, like the following:

"R24U26L24(LD26),"

Each string contains certain letters that should be followed by a number and there are parentheses() around any errors.

I want to find all the errors in each vector string - aka. any part of the string with a format like %(LD%)% or %(L%D)% [% being a wildcard] - and delete the pair of parentheses around the error, as well as any letter inside that has no number following it.

The result should look like this "R24U26L24D26,"

I've tried combining UPDATE, CASE ELSE, and REPLACE functions to run the different error criteria I have but have not succeeded. For example, the code I used below will replace the entire vector string with "%D%%" as text. Any help to figure this out is much appreciated.

UPDATE table
SET sketch = CASE 
WHEN sketch LIKE '%(LD%)%' THEN REPLACE(REPLACE('%(LD%)%', '(L', ''), ')', '')
WHEN sketch LIKE '%(RU%)%' THEN REPLACE(REPLACE('%(RU%)%', '(R', ''), ')', '')
WHEN sketch LIKE '%(L%U)%' THEN REPLACE(REPLACE('%(L%U)%', '(', ''), 'U)', '')
WHEN sketch LIKE '%(L%D)%' THEN REPLACE(REPLACE('%(L%D)%', '(', ''), 'D)', '')
ELSE sketch END;

Edit: This is the final fix that I used. Not as concise as I'd hoped but it works:

UPDATE table
SET sketch = regexp_replace(sketch, '\(LD([0-9]*)\)','D\1', 'g');

UPDATE table
SET sketch = regexp_replace(sketch, '\(RU([0-9]*)\)','U\1', 'g');

UPDATE table
SET sketch = regexp_replace(sketch, '\(L([0-9]*)U\)','L\1', 'g');

UPDATE table
SET sketch = regexp_replace(sketch, '\(L([0-9]*)D\)','L\1', 'g');

alvesoaj · Accepted Answer · 2019-02-26 12:02:22Z

1

Give it a try with:

UPDATE table_name
SET sketch = regexp_replace(sketch, '\(LD([0-9]*)\)','L\1');

UPDATE table_name
SET sketch = regexp_replace(sketch, '\(RU([0-9]*)\)','R\1');

UPDATE table_name
SET sketch = regexp_replace(sketch, '\(L([0-9]*)U\)','\1U');

UPDATE table_name
SET sketch = regexp_replace(sketch, '\(L([0-9]*)D\)','\1D');

It you want a single query, I guess one of these may works:

UPDATE table_name
SET sketch = (CASE
WHEN regexp_matches(sketch, '\(LD([0-9]*)\)') THEN regexp_replace(sketch, '\(LD([0-9]*)\)','L\1')
WHEN regexp_matches(sketch, '\(RU([0-9]*)\)') THEN regexp_replace(sketch, '\(RU([0-9]*)\)','R\1')
WHEN regexp_matches(sketch, '\(L([0-9]*)U\)') THEN regexp_replace(sketch, '\(L([0-9]*)U\)','\1U')
WHEN regexp_matches(sketch, '\(L([0-9]*)D\)') THEN regexp_replace(sketch, '\(L([0-9]*)D\)','\1D')
ELSE sketch END);

Or

UPDATE table_name
SET sketch = (CASE
WHEN sketch ~* '\(LD([0-9]*)\)' THEN regexp_replace(sketch, '\(LD([0-9]*)\)','L\1')
WHEN sketch ~* '\(RU([0-9]*)\)' THEN regexp_replace(sketch, '\(RU([0-9]*)\)','R\1')
WHEN sketch ~* '\(L([0-9]*)U\)' THEN regexp_replace(sketch, '\(L([0-9]*)U\)','\1U')
WHEN sketch ~* '\(L([0-9]*)D\)' THEN regexp_replace(sketch, '\(L([0-9]*)D\)','\1D')
ELSE sketch END);

edited Feb 26, 2019 at 12:02

answered Feb 25, 2019 at 19:01

alvesoaj

5626 silver badges17 bronze badges

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

5 Comments

Mia Over a year ago

Thanks, I had to switch the last part of the expression from '(\1D)' to '(L\1)' in order to remove the letter with no number attached. Also, the parentheses are still there. Any idea of how to remove those in the same update as well?

alvesoaj Over a year ago

The parenthesis, yes, sorry. I have insertes them in the solution. To remove the parentheses too, just remove it from the third param, for example: UPDATE table SET sketch = regexp_replace(sketch, '(LD([0-9]*))','L\1'); I will edit my response.

Mia Over a year ago

No worries! :) When I remove the parenthesis from the third param, for some reason it only works when there are four characters inside like (LD26) but not for (LD6). Also, the second query generally works but the first resulted in the following error message: set-returning functions are not allowed in UPDATE...WHEN regexp_matches.

Mia Over a year ago

I posted the final fix that I used above. Thanks so much for your help!

alvesoaj Over a year ago

I'm happy for help!

Collectives™ on Stack Overflow

Postgresql: Find and replace multiple characters within parentheses in string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related