How to split these records into individual new ones?

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:

And make it like this:

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 :

I want it to be :

• 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).

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.