SQLite string search in rows then insert string into new rows with values of the same row last column

Question

I want to provide a query to my database that will 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.

in the bellow example i want to get all of the the spicific 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 chanegable)

the current first row is like this: with one ticket_id and many site_ids(i only need site ids like:g1231 or g1236 and not the addresses in the 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 already search through the whole second column and find the 5-word site id's with regex (if u want to try:[A-Z]\d{1,4}), but i cant extract them from the row and insert each of them into a new row, my current code is like this:

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

but the above code will find the row that has the site_id's and insert all of the rows that will match the search and i dont want that. can someone help to convert the first one to the second? basically the current db is like this:

culumn1 | column2
ticket1 | many site ids
ticket2 | many site ids

but i want it like this:

culumn1 | 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 many site_ids for each ticket(i can already find them with regex) that need to be seperated to new rows like the above mentioned.

-it needs to be done in sqlite db browser and db browser only (its assigned like that and must be done like that so 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.