CODEWITHSUNDEEP
mysqlstringsubstring
Hadrian
Hadrian

Reputation: 165

Split comma separated values in MySQL

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

Upvotes: 3

Views: 7872

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 2

Related Questions