Split comma separated string into rows in mysql

Question

When I have string list like 1, 2, 3... I'd like to use this as one column

Ids
1
2
3

Is it possible by sql query?

ex) SELECT Ids from (1, 2, 3...) <- I know this is not working.

Answer 1

Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (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) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

Answer 2

For MySQL 8.0.4+

SELECT *
FROM
  JSON_TABLE(
          CONCAT('[', '1,2,3,4', ']'),
          "$[*]"
          COLUMNS(
              ids BIGINT(20) PATH "$"
              )
      ) AS tt

Concatenate square brackets ([]) around your string to make it into a JSON array. Then use JSON_TABLE to convert it into a table. See the MySQL JSON Table Functions for more info.

Answer 3

You can use below stored procedure to split string delimted by any character:

CREATE PROCEDURE `split_delimited` (
IN inputstr NVARCHAR(1000),
IN delimiter CHAR(1)
)
BEGIN
DROP TEMPORARY TABLE Items;
CREATE TEMPORARY TABLE Items(item NVARCHAR(50)); 
WHILE LOCATE(delimiter,inputstr) > 1 DO
INSERT INTO Items SELECT SUBSTRING_INDEX(inputstr,delimiter,1);
SET inputstr = REPLACE (inputstr, (SELECT LEFT(inputstr,LOCATE(delimiter,inputstr))),'');
END WHILE;
INSERT INTO Items(item) VALUES(inputstr);
select * from Items;
END

Input: 'a,b,c' Output: a b c

Answer 4

Note, this answer is probably not compatible with the released versions of MySQL available at the time the question was asked. I added it for future references for those interested in using a recursive CTE approach. Furthermore, irregular data have been added to test the exception handling.

create table test(ids varchar(50));
insert test values('1,2,3');

-- Note: the next_start_pos is the position to the immediate right of the comma.

with recursive cte as
    (select locate(',',ids,1)+1 as next_start_pos,
    substring(ids,1,locate(',',ids,1)-1) as i
    from test 
    union
    select locate(',',t.ids,next_start_pos)+1 ,
    case when locate(',',t.ids,next_start_pos) !=0 then substring(t.ids,next_start_pos,locate(',',t.ids,next_start_pos)-next_start_pos)
    else  substring(t.ids,next_start_pos) end 
    from test t join cte c
    where next_start_pos!=1
    )
select next_start_pos, i from cte where i !='';

+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              3 | 1    |
|              5 | 2    |
|              1 | 3    |
+----------------+------+

Note, in the main query which queries the cte result table, the where i !='' should be included as a foolproof feature. It circumvents result of empty space caused by leading/trailing comma or consecutive comma. Take the case below for instance:

truncate test;
insert test values(',,,1,,2,3,,,,'); 

-- run the same query again and we get:
+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              6 | 1    |
|              9 | 2    |
|             11 | 3    |
+----------------+------+

-- if the WHERE clause is removed, then we get:
+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              2 |      |
|              3 |      |
|              4 |      |
|              6 | 1    |
|              7 |      |
|              9 | 2    |
|             11 | 3    |
|             12 |      |
|             13 |      |
|             14 |      |
|              1 |      |
+----------------+------+