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
– eggyal, Commented Oct 13, 2012 at 10:38
@eggyal Thanks. I could do that But want to know whether it is possible in mysql.... — mymotherland
– mymotherland, Commented Oct 13, 2012 at 10:46
It can be done in mysql but too as eggyal said, its messy. Is php an option? — itachi
– itachi, Commented Oct 13, 2012 at 10:46
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
– Jim Leask, Commented Oct 1, 2021 at 10:50

Vignesh Kumar A · Accepted Answer · 2014-06-16 11:27:55Z

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, 2014 at 11:27

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jim Leask Over a year ago

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 Over a year ago

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 Over a year ago

BTW - It works for up to 100 comma separated values, not just 9. It could easily be extended to more if needed.

Juan Carlos Constantine · Accepted Answer · 2022-04-20 17:08:21Z

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')

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related