CODEWITHSUNDEEP
regexpostgresql
knoll
knoll

Reputation: 295

PSQL Regex match transforming many to one

How can I transform the results of this query:

select regexp_matches('number: 772392 number: 911604 number:123456',  '[0-9]{6}', 'g');

From this:

{772392}
{911604}
{123456}

To this?:

{772392,911604,123456}

I've tried transforming it via array_to_string, and array_agg, but I haven't been successful.

select array_to_string
(array_agg(regexp_matches
('number: 772392 number: 911604 number:123456', '[0-9]{6}', 'g')), ', ');

Upvotes: 0

Views: 342

Answers (1)

mantigatos
mantigatos

Reputation: 296

regexp_matches returns set of text arrays

If the pattern contains no parenthesized subexpressions, then each row returned is a single-element text array containing the substring matching the whole pattern.

so you should 

select string_agg(numb[1], ', ') 
    from regexp_matches('number: 772392 number: 911604 number:123456', '[0-9]{6}', 'g')
    as numb;

to achieve your goals

Upvotes: 3

Related Questions