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, Jul 17 '18 at 12:52
@strawberry Appreciate that as a best practice, these values will come into temporary tables from an integration software — Hadrian, Jul 17 '18 at 12:53
Well, the fact that you may have duplicated values in one column makes this pretty tricky. — Strawberry, Jul 17 '18 at 12:59
@Strawberry if all 3 columns have duplicate values, we can use distinct from select query — Hadrian, Jul 17 '18 at 13:01

Strawberry · Accepted Answer · 2018-07-17T13:22:04.713

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 '18 at 13:22

answered Jul 17 '18 at 13:02

Strawberry

33,750
13
40
57

It is returning same values as i have mentioned with my approach in the question, need to get multiple values into each column – Hadrian Jul 17 '18 at 13:06
This works fine for me. And you definitely do not need 'to get multiple values into each column' – Strawberry Jul 17 '18 at 13:16
I think its due to varchar values in some fields – Hadrian Jul 17 '18 at 13:19
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. – Hadrian Jul 17 '18 at 13:22
It makes no difference. – Strawberry Jul 17 '18 at 13:24
i have tested this and works fine, accepting your answer. Once again thank you for the solution – Hadrian Jul 17 '18 at 13:26
You're welcome, but remember that this is a 'sticking plaster' solution. You have to find a way to fix the way data is coming into your database. – Strawberry Jul 17 '18 at 13:29

Split comma separated values in MySQL

1 Answers1

Linked