Dealing with data stored as arrays in a MySQL DB
Just as I was about to post a comment that this looked like no serialization format I've ever seen, I had a lucky flash of insight.  (What programming language would make it altogether too easy to stash an array in a string like that? Ah...)

I think you may find that what you're looking at is the output of the serialize() function in PHP... the companion function is unserialize(). 

I'm not aware of a mechanism for deserializing this natively in MySQL (but then again, I wasn't aware of the whole thing a few minutes ago)... but if I had to do it natively in MySQL, I would go to the source code of common_schema for ideas and helper functions like get_num_tokens(), which returns a count of the number of tokens found in a given string of delimited text.  There is genuine outside-the-box genius lurking in common_schema.

Untwizzling scalar values into rows is not something easily done in MySQL but prettify_message() in common_schema provides an example of how it is technically possible... by splitting the strings and writing what you find in to a new table, it seems possible and maybe even borderline practical if you're not dealing with a massive data set.  Figure that part out and you could theoretically even build a trigger to keep your more-properly-structured table syncronized whenever one of those serialized columns is updated.

Or you could write something in php to read from the database, deserialize the arrays, iterate them, and stuff a table, but doing it natively in MySQL would be much more fun and at least offers the potential to keep another table always-current when you need it without an external script.