Parse text data in PostgreSQL

Question

I've got a PostgreSQL database, one table with 2 text columns, stored data like this:

id|         col1              |                     col2                      |
------------------------------------------------------------------------------|
1 | value_1, value_2, value_3 | name_1(date_1), name_2(date_2), name_3(date_3)|
2 | value_4, value_5, value_6 | name_4(date_4), name_5(date_5), name_6(date_6)|

I need to parse rows in a new table like this:

id |  col1   |  col2  |  col3  |
1  | value_1 | name_1 | date_1 |
1  | value_2 | name_2 | date_2 |
...|   ...   |  ...   |  ...   |
2  | value_6 | name_6 | date_6 |

How might I do this?

S-Man · Accepted Answer · 2021-02-01 18:57:22Z

step-by-step demo:db<>fiddle

SELECT
    id,
    u_col1 as col1,
    col2_matches[1] as col2,                                     -- 5
    col2_matches[2] as col3
FROM 
    mytable,
    unnest(                                                      -- 3
        regexp_split_to_array(col1, ', '),                       -- 1
        regexp_split_to_array(col2, ', ')                        -- 2
    ) as u (u_col1, u_col2),
    regexp_matches(u_col2, '(.+)\((.+)\)') as col2_matches       -- 4

Split the data of your first column into an array
Split the data of your second column into an array of form {a(a), b(b), c(c)}
Transpose all array elements into own records
Split the elements of form a(b) into an array of form {a,b}
Show required columns. For the col2 and col3 show the first or the second array element from step 4

Collectives™ on Stack Overflow

Parse text data in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related