Postgresql regular expression matching function: regexp_matches

Question

Given a string I want to extract all expression matching a regexp (e.g. email) as an array. Here is my actual code, using PostgreSQL 9.4 :

select regexp_matches('[email protected] lorem ipsum [email protected]',
                      '([a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4})',
                      'g')

The output is 2 records:

 regexp_matches  
 -----------------
 {[email protected]}
 {[email protected]}
 (2 rows)

What I want is to have all the matches in one array e.g.:

regexp_matches  
-----------------
{[email protected], [email protected]}
(1 row)

How to achieve that?

Craig Ringer · Accepted Answer · 2015-06-26 10:43:42Z

6

You can unnest each result array, then array_agg the lot of them together.

It's a bit ugly:

select array_agg(x)
from (
    select unnest(
       regexp_matches('[email protected] lorem ipsum [email protected]',
                      '([a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4})',
                      'g')
    )
) a(x);

answered Jun 26, 2015 at 10:43

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sdia Over a year ago

Thank you for the answer @CraigRinger, I will use your suggestion and create a function.

Collectives™ on Stack Overflow

Postgresql regular expression matching function: regexp_matches

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related