extract, match and combine information from string into array in PostgreSQL

Question

Problem

(sorry, had problems, cutting it into smaller pieces):

I have the following one column table A

                    s                            
-------------------------------------------
 (AAB) Some name1 1234 (XY*) Hello world 12
 (BB) Just one 123

and a lookup table L

 a1  | a2  
-----+-----
 XY* | XY
 AAB | A2B

I want to :

retrieve the parts in the brackets: (AAB, XY*)
get the corresponding a2 entry from the lookup table: (A2B, XY)
retrieve the number part of the entries: (1234, 12)
combine the results from 2 and 4 into an array: (A2B1234, XY12)

The results would be

                    s                            
-------------------------------------------
 {A2B1234, XY12}
 {BB123}

What I tried

This is how far I got:

For 1:

SELECT array_to_string(regexp_matches(s, '\((.*?)\)', 'g'), '') as in_bracket FROM A;
For 3:

SELECT array_to_string(regexp_matches(s, '(\d+)', 'g'), '') as numbers FROM A;

Here the struggle begins. How can I

Replace the values (in_bracket) with the lookup values on the fly?
Combine the results from the two select clauses into one array per row?

The data:

CREATE TABLE A (
  s VARCHAR
);
INSERT INTO a VALUES
  ('(AAB) Some name1 1234 (BB) More text 99 (XY*) Hello world 12'),
  ('(BB) Just one 123');

CREATE TABLE L (
  a1 VARCHAR(4),
  a2 VARCHAR(4)
);
INSERT INTO L VALUES 
  ('XY*', 'XY'),
  ('AAB', 'A2B');

Adam · Accepted Answer · 2016-10-06 15:48:44Z

1

I came to this form:

WITH parts AS (
    SELECT
        id,
        (regexp_matches(s, '\(([^\)]+)\)[^0-9]*([0-9]+)', 'g'))[1] AS search,
        (regexp_matches(s, '\(([^\)]+)\)[^0-9]*([0-9]+)', 'g'))[2] AS number
    FROM
        A
)
SELECT
    array_agg(L.a2 || parts.number)
FROM
    parts
    JOIN L
    ON (L.a1 = parts.search)
GROUP BY
    parts.id;

The output is:

{A2B1,XY12}
{BB123}

There are some thing that needs to be clarified:

what exactly is "number part of the entries" - in given example there is "name1", so my regex matches the "1" - note that A2B1 is returned instead of A2B1234
in table L there is no entry for BB - I have added it manually to my test L table - should just text in brackets be used in such situation?
I needed to group result from table A, and I have assumed that there is id column - you can also use s column for that provided that it is quniue

You may need to adjust the regex I've used. Here is an explanation to my version:

\(         - '('
([^\)]+)   - match of sequence of any char except ')' - this will be for example `AAB` or `XY*`
\)         - ')'
[^0-9]*    - any sequence of chars except digits (no-matching group)
([0-9]+)   - match of non-empty sequence of digits

edited Oct 6, 2016 at 15:48

answered Oct 6, 2016 at 15:36

Adam

5,6296 gold badges35 silver badges41 bronze badges

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

1 Comment

Mark Heckmann Over a year ago

SQL power in action! :) This seems to work perfectly. In my scenario it may happen, that there is no corresponding value in the lookup table. But a simple left join will take care of this and only return the values I need to extract. Thanks a ton!

Collectives™ on Stack Overflow

extract, match and combine information from string into array in PostgreSQL

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