Split comma separated values in MySQL

Question

I am trying to split comma-separated (,) values into multiple columns from a string

Sample data:

     COL1                 COL2            COL3
000002,000003,000042   09,31,51      007,004,007

Expected output:

Pno       Cno  Sno
000002    09   007
000003    31   004
000042    51   007

I have tried the following query:

SELECT   SUBSTRING_INDEX(COL1, ',', 1) Pno
                 ,SUBSTRING_INDEX(COL2, ',', 1) Cno
                 ,SUBSTRING_INDEX(COL3, ',', 1) Sno
            FROM MyTargetTable

Result:

Pno        Cno  Sno
000002     09   007

I might get more than 3 rows in each columns, I would like to know if there is any way to achieve this without specifying the substring position.

Possible input data could also be like this

     COL1                        COL2             COL3
000002,000003,000042,,000002   09,31,51,,32      007,004,007,,012

Simple fix: DON'T have separated values. Normalise your schema! — Strawberry
– Strawberry, Commented Jul 17, 2018 at 12:52
@strawberry Appreciate that as a best practice, these values will come into temporary tables from an integration software — Hadrian
– Hadrian, Commented Jul 17, 2018 at 12:53
Well, the fact that you may have duplicated values in one column makes this pretty tricky. — Strawberry
– Strawberry, Commented Jul 17, 2018 at 12:59
@Strawberry if all 3 columns have duplicate values, we can use distinct from select query — Hadrian
– Hadrian, Commented Jul 17, 2018 at 13:01

Strawberry · Accepted Answer · 2018-07-17 13:22:04Z

2

Here's one idea. It assumes you have a table of integers (ints) with values (i) 0-9...

SELECT DISTINCT
       SUBSTRING_INDEX(SUBSTRING_INDEX(col1,',',i+1),',',-1)x
     , SUBSTRING_INDEX(SUBSTRING_INDEX(col2,',',i+1),',',-1)y
     , SUBSTRING_INDEX(SUBSTRING_INDEX(col3,',',i+1),',',-1)z
  FROM my_table
     , ints
 ORDER 
    BY i;

edited Jul 17, 2018 at 13:22

answered Jul 17, 2018 at 13:02

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

7 Comments

Hadrian Over a year ago

It is returning same values as i have mentioned with my approach in the question, need to get multiple values into each column

Strawberry Over a year ago

This works fine for me. And you definitely do not need 'to get multiple values into each column'

Hadrian Over a year ago

I think its due to varchar values in some fields

Hadrian Over a year ago

This absolutely works perfect with only having int values and referencing int table, i would like to find a solution with varchar values in strings.

Strawberry Over a year ago

It makes no difference.

|

Collectives™ on Stack Overflow

Split comma separated values in MySQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related