retrieve and combine chunks of information from string using regex in postgres

Question

I need to retrieve information from structured text fragments which have the following format:

(AAB) Some name1 here 1234 (BB) More 12-text 99 (XY*) Hello world 12

What I want to get out is the following: {AAB1234, BB99, XY*12}

Strategy:

1. get characters inside brackets [e.g. (XY*)]
2. get last group of digits which is either followed by brackets or the end of string [e.g. 1234 (]

I did not get very far, as my regex skills are fairly limited.

SELECT regexp_matches('(AAB) Some name1 1234 (BB) More text 99 (XY*) Hello world 12',
          '\((.*?)\).*?(\d+)', 'g');

Giving

{AAB,1}
{BB,9}
{XY*,1}

Any ideas?

Add-on question:

I have the above text information in a column information in table my_table and I want to write the results into column results. How can I integrate the above solution into an UPDATE statement? I.e.

UPDATE my_table SET results = ???.

Answer 1

You may try:

SELECT array_agg(v) FROM (
    SELECT array_to_string(
               regexp_matches(
                   '(AAB) Some name1 1234 (BB) More text 99 (XY*) Hello world 12',
                   '\((.*?)\).*?(\d+)(?=$| \()', 'g'
               ),
               ''
           ) as v
    ) s;

Note that as usual, regexps can be quite fragile if you don't have a very formal definition of the syntax.

EDIT

To update, this should do the trick:

UPDATE my_table SET results = ARRAY(
    SELECT array_to_string(
               regexp_matches(
                   information,
                   '\((.*?)\).*?(\d+)(?=$| \()', 'g'
               ),
               ''
           )
    );

It expects results to be of type text[]. Alternatively, you could store the results as a string by adding an array_to_string.