Break up data from one column into multiple columns in a new table (MySQL)

Question

I have a table full of data, where one column is full of different entries for each row, where the data is formatted like this: A:some text|B:some other text|C:some more text| I want to separate those strings of text into two columns on a new table. So the new table should have one column for A, B, C etc. and the other column will have the rest of the text in their respective rows. And there is another value (a DATETIME value) in a separate column of the first table that I would like to copy into a third column for each of the separated entries.

Let me know if this needs clarificaiton, I know it's kind of confusing and I'm pretty fuzzy with MySQL. Thanks!

Answer 1

MySQL supports SUBSTRING, together with LOCATE you could probably whip up something nice, based on the pipe symbol you seem to use as a separator.

• http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_locate
• http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring

In most cases I prefer to write "convertors" in a another language than perform it directly on the database, however in this situation it looks like it's not that much data so 'might' work fine..

Answer 2

I think you should better write a simple script in VBScript, PHP or any other scripting language of your choice. All scripting languages provide you with string manipulation and date formatting functions. Database queries won't allow you to handle the "unexpected".