Split comma separated value from table column into rows using mysql?

Question

I found the post which has the column with 3 values seperated by commas. I will have more than 3 values seperated by commas . For example,

ID    CITY_ID
--------------
1     12,18,16,17,35
2     12,16
....
--------------

Is it possible to split string city_id and make it into rows using mysql query like below

  ID   CITY_ID
  -------------
   1    12
   1    18
   1    16
    ....
   2    16
    ....
  --------------

If yes, Kindly give some advice ....

You can write a procedure to do it, but it's pretty messy. You may as well implement it in PHP by looping over each record. — eggyal, Oct 13 '12 at 10:38
@eggyal Thanks. I could do that But want to know whether it is possible in mysql.... — mymotherland, Oct 13 '12 at 10:46
It can be done in mysql but too as eggyal said, its messy. Is php an option? — itachi, Oct 13 '12 at 10:46
So , you have a solution with PHP and can close this question or still need help with the php script? — Ofir Baruch, Oct 13 '12 at 13:04
Please mark the answer below as the correct answer. Others need to be able to find it, as many ask and nothing comes close to being this simple and working this well. — Jim Leask, Oct 01 '21 at 10:50

score 8 · Answer 1 · answered Jun 16 '14 at 11:27

8

Try this

  SELECT ID,SUBSTRING_INDEX(SUBSTRING_INDEX(t.CITY_ID, ',', 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.CITY_ID) - LENGTH(REPLACE(t.CITY_ID, ',', '')))
 ORDER BY ID,value

FIDDLE DEMO

Output:

answered Jun 16 '14 at 11:27

Vignesh Kumar A

27,863
13
63
115

This works surprisingly well! I need to debug a bit to see if it is limited to 9 entries or not, but it looks like it will be fine for my purposes. – Jim Leask Sep 29 '21 at 16:00
This is such a brilliant piece of SQL. Many people are looking for this in other threads but haven't found it. I wish I could give you more up votes. – Jim Leask Oct 01 '21 at 10:49
BTW - It works for up to 100 comma separated values, not just 9. It could easily be extended to more if needed. – Jim Leask Oct 01 '21 at 10:49

score 0 · Answer 2 · answered Apr 20 '22 at 17:08

Reference: https://gist.github.com/duanehutchins/c6000b367b3032b0b495c46b3bc370c8

Functions:

DROP FUNCTION IF EXISTS `SET_COUNT`;
        
CREATE FUNCTION `SET_COUNT`($strlist MEDIUMBLOB) RETURNS SMALLINT(5) UNSIGNED
NO SQL
DETERMINISTIC
RETURN 1+CHAR_LENGTH($strlist)-CHAR_LENGTH(REPLACE($strlist,',',''));
              
DROP FUNCTION IF EXISTS `SET_EXTRACT`;
             
CREATE FUNCTION `SET_EXTRACT`($i SMALLINT UNSIGNED, $strlist MEDIUMBLOB) RETURNS VARBINARY(255)
NO SQL
DETERMINISTIC
RETURN NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(0b0, ',', $strlist, ',', 0b0), ',', $i+1.5*(SIGN($i+0.5)+1)-1), ',', -SIGN($i+0.5)),0b0);

use:

  SELECT SET_EXTRACT(n.digit, 'a,b,c,d,f,g')AS split_value FROM  (SELECT 0 digit 
  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
  UNION ALL SELECT 10) AS  n WHERE n.digit < set_count('a,b,c,d,f,g')

Split comma separated value from table column into rows using mysql?

2 Answers2

Linked