Moving a CSV-style varchar column into a new table

Question

Database I created 15 years ago was not well designed. I have a varchar column with a string of comma delimited values: '5,8,13'. The string contains no spaces and no trailing commas but the number of values are variable. What I'd like to do is create a new table and move these values into it paired with that row's ID. Assume the above values are from a row with an ID of 7, the result would be 3 rows:

[
   {7, 5}
   {7, 8}
   {7, 13}
]

Looking at the existing data set, the column has anywhere from 1 to 6 values. I found this thread showing how I could do a split string function: Split value from one field to two

But frankly, I'm at a loss on how I'd go about turning this into a single insert. If it's not a simple task, then I'll just write something in PHP even though it'll be a lot of insert statements.

Further explanation, here are two tables:

create table Table_A(
    id int auto_increment primary key,
    platforms varchar(255)
)

create table Table_B(
    id int auto_increment primary key,
    platform int not null
)

Table_A is the existing data where the data in platforms column is a comma separated value of numbers "3,45," or "56,4". These numbers point to indexes in another table. yes I know poor design, thankfully I've learned better since those days. I want to parse the numbers from the platform string in Table_A and insert them into Table_B along with the ID of said Table_A row.

i a little at a loss, how you want ypur data, please add also a full [mre] — nbk, Aug 04 '21 at 19:16
Can you edit your question to show (as text, not an image) output of `show create table yourtablename` please, for both your previous and new tables — ysth, Aug 04 '21 at 19:32
I can't check the version at the moment from my current environment, it'll be an hour before I'm back over there. — Phaelax z, Aug 04 '21 at 19:40

score 2 · Accepted Answer · answered Aug 04 '21 at 19:57

Here is a little query to do that. it only splits 6 values from the CSV. if you have more VAls in a row you must change the UNION ALL

The Result was stored in singletable.

MariaDB [bernd]> DESCRIBE singletable;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| rno       | int(11)          | YES  |     | NULL    |                |
| singleval | int(11)          | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [bernd]> SELECT * FROM singletable;
Empty set (0.00 sec)

MariaDB [bernd]> 
MariaDB [bernd]> SELECT * FROM csvtable;
+----+----------+
| id | csvvals  |
+----+----------+
|  1 | 1,3,5    |
|  2 | 2,4      |
|  3 | 6        |
|  4 | 8,9      |
|  5 | 22,21,20 |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [bernd]> 
MariaDB [bernd]> 
MariaDB [bernd]> INSERT INTO singletable (rno,singleval)
    -> SELECT id as rno , SUBSTRING_INDEX( SUBSTRING_INDEX(csvvals, ',', no) ,',',-1) singleval
    -> FROM csvtable
    -> CROSS JOIN (SELECT 1 as no UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) as n
    -> WHERE no <= LENGTH(csvvals) - LENGTH(REPLACE(csvvals,',','')) +1
    -> ORDER BY id,no;
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [bernd]> SELECT * FROM singletable;
+----+------+-----------+
| id | rno  | singleval |
+----+------+-----------+
|  1 |    1 |         1 |
|  2 |    1 |         3 |
|  3 |    1 |         5 |
|  4 |    2 |         2 |
|  5 |    2 |         4 |
|  6 |    3 |         6 |
|  7 |    4 |         8 |
|  8 |    4 |         9 |
|  9 |    5 |        22 |
| 10 |    5 |        21 |
| 11 |    5 |        20 |
+----+------+-----------+
11 rows in set (0.02 sec)

MariaDB [bernd]>

Moving a CSV-style varchar column into a new table

1 Answers1