normalizing existing data; varchar list to integers

Question

Normalizing an existing table I made a decade ago. I have a varchar column (platforms) with a list of numbers that I'd like to break down and place into a separate table.

[table_A]
  id int
  platforms varchar
  other fields....

An example for platform would look something like:

"1,3,4,7"

Those numbers link to another table called Platforms, which is nothing more than a matching ID and a varchar field (a description). I've created a new table to make the associations between table_A and Platforms.

[Table_B]
  id int
  table_A_id int
  platform_id

So for the above platform sample, I'd have 4 rows inserted into Table_B. My question is, can I do this strictly through SQL? Only thing I can think of is writing a php script to parse the varchar and do individual inserts on each row in table_A. Is there a more elegant way without resorting to an external script?

Answer 1

INSERT INTO Table_B (table_A_id, platform_id)
 SELECT a.id, p.id
 FROM table_A AS a JOIN Platforms AS p ON FIND_IN_SET(p.ID, a.platforms);