CODEWITHSUNDEEP
mysqlsql
Bewan
Bewan

Reputation: 125

Split comma separated string into rows in mysql

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.

Upvotes: 12

Views: 34577

Answers (6)

TechFanDan
TechFanDan

Reputation: 3502

To add on @Dmitry's answer, when you have a field in a table that contains the delimited content:

SELECT 
    DISTINCT(jt.ids) AS id, jt.ids AS val
FROM
table_with_content r,
JSON_TABLE(
    CONCAT('["', trim(REPLACE(r.delimited_field_here,';','","')) , '"]'),
    "$[*]" 
    COLUMNS(
        ids VARCHAR(1000) PATH "$"
    )
) AS jt

Change the REPLACE to find the correct delimiter. The example above looks for semicolon.

Edit: note the distinct that will remove duplicates. This snippet was to read rows where a column was containing delimited content to return a list of unique entries.

Upvotes: -1

lisandro
lisandro

Reputation: 506

This works with any sanitized comma separated values.

SET @values = '1,Smith,3';
SELECT * from JSON_TABLE(CONCAT('["', REPLACE(@values, ',', '","'), '"]'),
                  '$[*]' COLUMNS (yourcolumn_name TEXT PATH '$')) jsontable;

query results in rows

Upvotes: 0

blabla_bingo
blabla_bingo

Reputation: 2162

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 |      |
+----------------+------+

Upvotes: 0

singhk
singhk

Reputation: 1

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

Upvotes: 0

Dmitry
Dmitry

Reputation: 7276

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.

Upvotes: 14

Mihai
Mihai

Reputation: 26804

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

Upvotes: 13

Related Questions