How to remove substrings that follow a pattern

Question

I have a query that returns a column with a string I need to clean up. I would like to remove the portion of this string that starts with ",id:" and ends right before ")". The string could have many occurrences of the substring to be removed. The alphanumeric substring that follows "id:" is always the same length.

This is a sample string:

(label:Boston,include:true,id:9fc53def-0b9d-45cb-8f12-d42c3ca70ab2),
(label:Cambridge,include:true,id:dda3d6d7-f9d1-45ac-ac6e-5866b356966e)

This is the string I would like to see at the end:

(label:Boston,include:true),
(label:Cambridge,include:true)

Is the string to remove always a UUID in standard Postgres output format? Lower case hex with 4 dashes. — Erwin Brandstetter, Apr 09 '21 at 12:52

Laurenz Albe · Accepted Answer · 2021-04-10T01:48:55.733

0

SELECT regexp_replace(
          '(label:Cambridge,include:true,id:dda3d6d7-f9d1-45ac-ac6e-5866b356966e)',
          ',?\mid:.{36}',  -- pattern
          '',            -- replacement
          'g'            -- flag: replace multiple occurrences
       );
         regexp_replace         
════════════════════════════════
 (label:Cambridge,include:true)
(1 row)

Explaining the pattern:

,? matches a comma, if there is one
\m matches the beginning of a word
.{36} matches 36 arbitrary characters

edited Apr 10 '21 at 01:48

answered Apr 09 '21 at 12:37

Laurenz Albe

209,280
17
206
263

Thanks @laurenz, worked like a charm. Could you help me understand the use of "?", "." and "{}" in the pattern? – Komsinica Apr 09 '21 at 16:46
I have added some explanation. – Laurenz Albe Apr 09 '21 at 16:49

score 0 · Answer 2 · answered Apr 09 '21 at 12:43

0

try this it will hepls you......

select SUBSTRING('label:Boston,include:true,id:9fc53def-0b9d-45cb-8f12-d42c3ca70ab2',1,LEN('label:Boston,include:true,id:9fc53def-0b9d-45cb-8f12-d42c3ca70ab2')-40) as stringaa;

answered Apr 09 '21 at 12:43

PrA-patel

99
1
2

The example string is one value, not two. The pattern can repeat multiple times within a single value. – Komsinica Apr 09 '21 at 13:59

Erwin Brandstetter · Answer 3 · 2021-04-09T13:13:46.283

starts with ",id:" and ends right before ")"

SELECT regexp_replace(string, ',id:[0-9a-f-]{36}\)', ')', 'g');

Neither comma nor closing parenthesis are optional in your description.

Or with an even more rigid pattern to match standard Postgres UUID format as described in the manual:

SELECT regexp_replace(string
                    , ',id:[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\)'
                    , ')', 'g');

How to remove substrings that follow a pattern

3 Answers3