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

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:E7089 or E7459 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)

CodePudding user response：

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.