SQL split comma separated row [duplicate]

Question

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)

Caelian

asked a gazillion times on SO: e.g.#10582272 , #13874201 – Gibbie

@MitchWheat - thats not an answer for this question. They have a set number of comma separated values, I do not. – Caelian

Are you looking for a pure SQL solution, or a solution in some other language - like PHP for example. – Avicenna

where does it say that in your question? – Gibbie

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. – Caelian

BTW: in the long run you would be better fixing " I have a column with comma seperated values" - i.e. 3NF – Gibbie

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. – Kava

Answer 1

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

Cohby

Great answer, worked perfectly to insert friend ids into a user relationship table (based on column containing friendid1,friendid2) – Bumptious

Works. Tip : Replace table1 -> [your table], values -> your field name, value -> your field name, nothing else is required. – Eleen

great solution works like charm, but how to group by ? – Honesty