How to split these records into individual new ones?

Question

I want to search if a string exists in column2 (site_id) then put that string in a new table with the value of its before column in that row. The table has only 2 columns and the site_id column may have many 5-word strings that I want.

I want to get all of the the specific site id's. For example: E7089 or E7459 (I need all of them and the first word is random like E or T or etc and the four digits are variable).

The first row is with one ticket_id and many site_ids. I only need site ids like:g1231 or g1236 and not the addresses in parentheses:

ticket_id	site_id
sss-bb-12312312-12312	g1231(afsdgf-sdgsdgdg), g1236(sdfsdgsdg), g3212(asdfas-dfsd), b2311(asdasd), b3213(asdfsdf)

And make it like this:

ticket_id	site_id
sss-bb-12312312-12312	g1231
sss-bb-12312312-12312	g3211
sss-bb-12312312-12312	g1236
sss-bb-12312312-12312	b2311
sss-bb-12312312-12312	b3213

I can find the 5-word site id's with regexp [A-Z]\d{1,4}, but I can't extract and insert them into a new row. My code :

DROP TABLE IF EXISTS test2;

CREATE TABLE if NOT EXISTS test2 
(
    Ticket_id varchar,
    site_id varchar
);

INSERT INTO test2 
    SELECT ticket_id, site_id 
    FROM TEST 
    WHERE site_id regexp '[A-Z]\d{1,4}';

This will find the site_id's and insert rows that match. I don't want that. How to convert the first one to the second?

Current db :

column1	column2
ticket1	many site ids
ticket2	many site ids

I want it to be :

column1	column2
ticket1	id
ticket1	id
ticket1	id
ticket1	id
ticket2	id
ticket2	id
ticket2	id

• The tickets do not need any change except getting copied into new rows with their assigned site_id.
• There are multiple site_ids for each ticket that need to be separated to new rows.
• It needs to be done in SQLite db browser (unfortunately no Python).

Answer 1

You need a recursive CTE to split the site_id column of the table test1 and SUBSTR() function to take the first 5 chars to insert in the table test2:

WITH cte AS (
    SELECT ticket_id, '' site_id, site_id || ',' s 
    FROM test1
    UNION ALL 
    SELECT ticket_id,
           SUBSTR(s, 0, INSTR(s, ',')),
           SUBSTR(s, INSTR(s, ',') + 1)
    FROM cte 
    WHERE s <> ''
) 
INSERT INTO test2 (ticket_id, site_id) 
SELECT ticket_id, SUBSTR(TRIM(site_id), 1, 5) 
FROM cte
WHERE site_id <> '';

See the demo.