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_id
s. 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_id
s 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 tabletest1
and SUBSTR() function to take the first 5 chars to insert in the tabletest2
:See the demo.