Postgresql regex to return multiple values

Question

I have a scenario in postgres where I need to gather all entries between multiple sets of square brackets.

The following example would be what I would expect to capture this:

SELECT (regexp_matches('Hello [World] How [Are] You','\[(.*?)\]')) 

But this simply returns

{World}

ignoring the second [Are] section.

In a regular regex this seems to work, so I'm unsure as to why its failing here.

Ideally, I would like to return the result as as csv text string. e.g.

World,Are

but I can't seem to find the right query to do this.

Any input appreciated. Thanks.

Answer 1

You have to use the 'g' flag

SELECT (regexp_matches('Hello [World] How [Are] You','\[(.*?)\]','g'))

The "g" flag indicates that the regular expression should be tested against all possible matches in a string.