Extract multiple values from a string for each id

Question

I want to extract matches from a string column for each id. How can I achieve that?

+--------+---------------------------------------+
|   id   |                 text                  |
+--------+---------------------------------------+
| fsaf12 | Other Questions,Missing Document      |
| sfas11 | Others,Missing Address,Missing Name   |
+--------+---------------------------------------+

Desired output:

+--------+------------------+
|   id   |    extracted     |
+--------+------------------+
| fsaf12 | Other Questions  |
| fsaf12 | Missing Document |
| sfas11 | Others           |
| sfas11 | Missing Address  |
| sfas11 | Missing Name     |
+--------+------------------+

Here is the query for sample data: FIDDLE

Answer 1

Postgres is not my forte at all but based on this older post on SO you could try to use unnest(). I included a TRIM() to remove possible railing spaces after a split:

SELECT id, TRIM(unnest(string_to_array(text, ','))) as "extracted" FROM t1;

Or, if you want to use regexp_split_to_table():

SELECT id, regexp_split_to_table(text, '\s*,\s*') as "extracted" FROM t1;

Here we matches 0+ whitespace characters, a literal comma and again 0+ whitespace characters.

Answer 2

You can use regexp_split_to_table for your requirement like below:

WITH t1 AS (
    SELECT 'fsaf12' AS id, 'Other Questions,Missing Document' AS text UNION ALL
    SELECT 'sfas11', 'Others,Missing Address,Missing Name'
)
SELECT id, regexp_split_to_table(text,',')
FROM t1

OUTPUT

| id        | extracted             |
|-----------|-----------------------|
| fsaf12    | Other Questions       |
| fsaf12    | Missing Document      |
| sfas11    | Others                |
| sfas11    | Missing Address       |
| sfas11    | Missing Name          |

DEMO