Postgresql regex returns multiple values

Question

I have a script in postgres where I need to collect all the records between multiple sets of square brackets.

The following example would be what I would expect for this:

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

But it just returns

{World}

ignoring the second section [Are].

In regex, this seems to work, so I'm not sure why it doesn't work here.

Ideally, I would like to return the result as a csv text string. eg.

World,Are

but I can not find the correct query for this.

Any input is appreciated. Thank you

Answer 1

You must use the flag 'g'

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

The g flag means that the regular expression should be checked against all possible matches in the string.