SQL split values to multiple rows

by

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

More Related Contents:

  1. MySQL: Split comma separated list into multiple rows
  2. If two columns are 1 then make new column
  3. How can I return pivot table output in MySQL?
  4. How to find similar results and sort by similarity?
  5. MySQL error: key specification without a key length
  6. Unknown column in ‘field list’ error on MySQL Update query
  7. Insert into multiple tables in one query
  8. generate_series() equivalent in MySQL
  9. MySQL : isn’t in GROUP BY
  10. MySQL: Can’t create table (errno: 150)
  11. Mysql query to dynamically convert rows to columns
  12. Why does MySQL report a syntax error on FULL OUTER JOIN?
  13. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  14. Mixing ANSI 1992 JOINs and COMMAs in a query
  15. SQL: Creating a Relational table with 2 different auto_increment
  16. How do I add a custom CHECK constraint on a MySQL table?
  17. MySQL variable format for a “NOT IN” list of values
  18. Convert partially non-numeric text into number in MySQL query
  19. Order by COUNT per value
  20. Select from one table where not in another
  21. How to select the most recent set of dated records from a mysql table
  22. Second SELECT query if first SELECT returns 0 rows
  23. Set value to NULL in MySQL
  24. SELECT query return 1 row from each group
  25. MySql Single Table, Select last 7 days and include empty rows
  26. mysql SQL: specific item to be first and then to sort the rest of the items
  27. When is a timestamp (auto) updated?
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment