21

I have a column with a variable number of comma seperated values:

somethingA,somethingB,somethingC
somethingElseA, somethingElseB

And I want the result to take each value, and create a row:

somethingA
somethingB
somethingC
somethingElseA
somethingElseB

How can I do this in SQL (MySQL)?

(I've tried googling "implode" and "lateral view", but those don't seem to turn up related questions. All the related SO questions are trying to do much more complicated things)

Don P
  • 60,113
  • 114
  • 300
  • 432
  • asked a gazillion times on SO: e.g.http://stackoverflow.com/questions/10581772/sql-server-2008-how-to-split-a-comma-separated-value-to-columns , http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows – Mitch Wheat Sep 29 '13 at 01:30
  • 1
    @MitchWheat - thats not an answer for this question. They have a set number of comma separated values, I do not. – Don P Sep 29 '13 at 01:31
  • Are you looking for a pure SQL solution, or a solution in some other language - like PHP for example. –  Sep 29 '13 at 01:31
  • where does it say that in your question? – Mitch Wheat Sep 29 '13 at 01:31
  • If you're going to mark it as "close", please actually link to the answer instead of just assuming it's a commonly asked question with an answer. – Don P Sep 29 '13 at 01:32
  • BTW: in the long run you would be better fixing " I have a column with comma seperated values" - i.e. 3NF – Mitch Wheat Sep 29 '13 at 01:33
  • When working on issues like this, responses like "better fixing " I have a column with comma separated values" - i.e. 3NF" are profoundly unhelpful. Maybe I'm in the minority, but I don't think most devs design this way because they want to. In my case I'm interfacing with a legacy ERP system that isn't RD based and I have no choice, because the client probably doesn't want to spend close to a million dollars replacing their ERP system because the data isn't in 3rd normal form. – Craig Jacobs Aug 16 '15 at 03:42

1 Answers1

68

You can do it with pure SQL like this

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

Note: The trick is to leverage tally(numbers) table and a very handy in this case MySQL function SUBSTRING_INDEX(). If you do a lot of such queries (splitting) then you might consider to populate and use a persisted tally table instead of generating it on fly with a subquery like in this example. The subquery in this example generates a sequence of numbers from 1 to 100 effectively allowing you split up to 100 delimited values per row in source table. If you need more or less you can easily adjust it.

Output:

|          VALUE |
|----------------|
|     somethingA |
|     somethingB |
|     somethingC |
| somethingElseA |
| somethingElseB |

Here is SQLFiddle demo

This is how the query might look with a persisted tally table

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN tally n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157