Mark Heckmann Mark Heckmann - 1 year ago 77
SQL Question

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

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}


  1. get characters inside brackets [e.g. (

  2. get last group of digits which is either followed by brackets or the end of string [e.g.

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');



Any ideas?

Add-on question:

We have the above text information in a column
in table
and we want to write the results into column 'results'. How can I integrate the above solution into an

UPDATE my_table SET results = ???.

Answer Source

You may try:

SELECT array_agg(v) FROM (
    SELECT array_to_string(
                   '(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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download