0

I have a table: 

 ID      name
 1       john, peter, ken

how can i separate the name column (which the value are being separated with comma delimited) into separate column using MYSQL? 

ID       name
1        john
1        peter
1        ken
Cœur
  • 37,241
  • 25
  • 195
  • 267
user1529419
  • 441
  • 2
  • 9
  • 19
  • Why the C# tag? This has nothing to do with C#. – It'sNotALie. Jun 23 '13 at 11:01
  • This question has been asked a million times here on SO. – fancyPants Jun 23 '13 at 11:16
  • possible duplicate of [How to Convert Comma Seperated Column into rows and add counter](http://stackoverflow.com/questions/14184529/how-to-convert-comma-seperated-column-into-rows-and-add-counter) or here with C# http://stackoverflow.com/questions/1273552/sql-split-comma-separated-column-on-delimiter?rq=1 – fancyPants Jun 23 '13 at 11:17
  • well i just found the answer like you have requested. Check the link http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/ – Dinup Kandel Jun 23 '13 at 11:25

2 Answers2

2

You could use a query like this:

SELECT
  id,
  n.digit,
  name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.digit+1), ',', -1)
FROM
  mytable
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(name, ',' , '')) <= LENGTH(name)-n.digit

(please notice that this will only separate up to 4 names).

Fiddle is here.

Explanation

I'm making use of a query that returns all numbers from 0 to 3 (you could use an indexed table with 10 or 100 numbers, for example):

SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

I'm then joining this number with mytable, using this join conditions:

ON LENGTH(REPLACE(name, ',' , '')) <= LENGTH(name)-n.digit

which is a "dirty" way to check if the name column contains at least n.digit names (I'm removing all commas, and I'm checking the difference in length with the full string).

Using SUBSTRING_INDEX you can get the part of the string at the left of the n.digit+1 comma:

SUBSTRING_INDEX(name, ',', n.digit+1)

and using one more SUBSTRING_INDEX with a negative parameter you can only keep the part of the string up to the first comma:

SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.digit+1), ',', -1)
fthiella
  • 48,073
  • 15
  • 90
  • 106
0

If you need to get thh data into mysql then you can create a php file using explode to create an array per id.

These may help:

How can I split a comma delimited string into an array in PHP?

http://php.net/manual/en/function.explode.php

Community
  • 1
  • 1
Adam G
  • 84
  • 2
  • 8