CODEWITHSUNDEEP
sqlregexpostgresql
JamesA
JamesA

Reputation: 375

Postgresql regex to return multiple values

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.

Upvotes: 4

Views: 1728

Answers (1)

javier_domenech
javier_domenech

Reputation: 6263

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.

Upvotes: 3

Related Questions