1

Possible Duplicate:
Can Mysql Split a column?

I have a single row with 1 column in mysql that has data in the form of 'Dave=>Anderson, Mick=>Jagger, Foxxy=>Brown'. Is there a way in mysql to split the string & iterate through each split & then subsequently format the data?

I have the following:

SELECT SUBSTRING_INDEX(  `names` ,  ',', 1 ) AS mynames FROM  `table`

This will obviously give me just 'Dave=>Anderson' and not the rest of the names.

(I realize I can do this in PHP but wanted to know if this can be done in mysql)

UPDATE: I should be clearer: I would like to return a separate row for each of the names:

    +-----------------+
    | mynames         |
    +-----------------+
    | Dave Anderson   |
    +-----------------+
    | Mick Jagger     |
    +-----------------+
    | Foxxy Brown     |
    +-----------------+
Community
  • 1
  • 1
user_78361084
  • 3,538
  • 22
  • 85
  • 147
  • 1
    In that case, you shouldn't store the entries like that in a single field. Split them off into their own 1-to-many table and they'd automatically be on seperate rows. – Marc B Jan 16 '11 at 02:42
  • i'd rather not do that...in my case it creates a couple hundred thousand tables, which makes my db hard to maintain – user_78361084 Jan 16 '11 at 02:52
  • 1
    100K+ tables - you mean rows right ? – Jon Black Jan 16 '11 at 03:41
  • Sounds like not having separate rows is making your DB hard to maintain. Having another table with a row for each name is how relational databases are designed to work. – Brendan Long Jan 16 '11 at 04:39

0 Answers0