Postgres regexp replace not working

Question

Im trying to create a regexp for this query:

SELECT gruppo
FROM righe_conto_ready
WHERE regexp_replace(gruppo,'(\[{1})|(\].*?\[)|(\].*$)','','g') = '[U6][U53]'
LIMIT 10

This is an example of 'gruppo' column:

[U6]  CAFFETTERIA   [U43]  THE E TISANE

Im currently using this query for testing:

SELECT regexp_replace(gruppo,'(\[{1})|(\].*?\[)|(\].*$)','','g') FROM ....

and it returns just U6

How can i change the regexp to remove everything outside brackets?

Answer 1

You can use regexp_matches() with the much simpler regular expression:

with righe_conto_ready(gruppo) as (
    select '[U6]  CAFFETTERIA   [U43]  THE E TISANE'::text
)

select gruppo
from righe_conto_ready, 
lateral regexp_matches(gruppo, '\[.+?\]', 'g') matches
group by 1
having string_agg(matches[1], '') = '[U6][U43]'

                 gruppo                  
-----------------------------------------
 [U6]  CAFFETTERIA   [U43]  THE E TISANE
(1 row) 

When you are looking for multiple matches of some pattern, regexp_matches() seems more natural than regexp_replace().

You can also search for first two substrings in brackets (without the g flag the function yields no more than one row):

select gruppo
from righe_conto_ready, 
lateral regexp_matches(gruppo, '(\[.+?\]).*(\[.+?\])') matches
where concat(matches[1], matches[2]) = '[U6][U43]'