SQL split comma separated row [duplicate]

by

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

More Related Contents:

  1. How do I limit the number of rows per field value in SQL?
  2. Recreating a query using INNER JOIN? [duplicate]
  3. Create a Cumulative Sum Column in MySQL
  4. How to perform grouped ranking in MySQL
  5. Equivalent of explode() to work with strings in MySQL
  6. Select row with most recent date per user
  7. difference between primary key and unique key
  8. Illegal mix of collations MySQL Error
  9. SQL query: Delete all records from the table except latest N?
  10. SQL order string as number
  11. Rename a column in MySQL
  12. Find most frequent value in SQL column
  13. How to convert number of week into date?
  14. The total number of locks exceeds the lock table size
  15. update multiple rows using limit in mysql?
  16. Mysql 1050 Error “Table already exists” when in fact, it does not
  17. MySQL “IN” operator performance on (large?) number of values
  18. How to get the latest record in each group using GROUP BY? [duplicate]
  19. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  20. SQL unique varchar case sensitivity question
  21. How do I count columns of a table
  22. Difference between two table structure
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. How to regex in a MySQL query
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Why MySQL is giving error “Not allowed to return a result set from a function”?
  27. When to use a left outer join?
  28. MySQL 5.6 – DENSE_RANK like functionality without Order By
  29. Insert and set value with max()+1 problems
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment