Split a column row into multiple column rows

Question

I have an entry in a table that is a row that is separated by a semicolon. Can I split a row into separate columns? I looked online and on stackoverflow, and I could not find one that would split into columns.

The entry in the table looks something like this (something in the brackets [] is actually not in my table. Just to make things more clear):

sysinfo [column] miscInfo ; vendor: aaa ; bootr: bbb; revision: ccc; model: ddd [string a] miscInfo ; vendor: aaa ; bootr: bbb; revision: ccc; model: ddd [string b] ... 

There are a little over a million entries with a line that looks like this. Is it possible in mySQL so that the query returns the following

  miscInfo, Vendor, Bootr, Revision , Model [columns] miscInfo_a, vendor_a, bootr_a, revision_a, model_a miscInfo_b, vendor_b, bootr_b, revision_b, model_b ... 

for all rows in the table, where does the comma indicate the new column?

Edit:

Here are some input and output requested by Bohemian.

 sysinfo [column] Modem <<HW_REV: 04; VENDOR: Arris ; BOOTR: 6.xx; SW_REV: 5.2.xxC; MODEL: TM602G>> <<HW_REV: 1; VENDOR: Motorola ; BOOTR: 216; SW_REV: 2.4.1.5; MODEL: SB5101>> Thomson DOCSIS Cable Modem <<HW_REV: 4.0; VENDOR: Thomson; BOOTR: 2.1.6d; SW_REV: ST52.01.02; MODEL: DCM425>> 

Some may be longer, but they all have the same format. Here is what I would like to receive:

 miscInfo, vendor, bootr, revision, model [columns] 04, Arris, 6.xx, 5.2.xxC, TM602G 1, Motorola, 216, 2.4.1.5, SB5101 4.0, Thomson, 2.1.6d, ST52.01.02, DCM425 

Answer 1

You can use string functions (especially substr ) in mysql: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Answer 2

Please see how I split the coordinate column into 2 lat / lng columns:

 UPDATE shops_locations L LEFT JOIN shops_locations L2 ON L2.id = L.id SET L.coord_lat = SUBSTRING(L2.coordinates, 1, LOCATE('|', L2.coordinates) - 1), L.coord_lng = SUBSTRING(L2.coordinates, LOCATE('|', L2.coordinates) + 1) 

In general, I followed the UPDATE JOIN advice from here MySQL - UPDATE query based on SELECT and STR_SPLIT here Separate the value from one field into two

Yes, I just break into 2, and SUBSTRING may not work for you, but I hope this helps anyway :)