I have comma separated data in a column:

Column 
------- 
a,b,c,d, n etc

I want to split the comma separated data into multiple columns to get this output:

Column1  Column2 Column3 Column4 ColumnN 
-------  ------- ------- ------- -------
a        b       c       d       n

How can this be achieved?

used Mysql 5.6.17

CodePudding user response：

You can write your own split function to do that.

CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT )
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1))   1),delim, '');

Use might like this, the second parameter determined which value of position you want to retrieve from the string.

SELECT
 SPLIT_STR(Column,',',1) AS a,
 SPLIT_STR(Column,',',2) AS b,
 SPLIT_STR(Column,',',3) AS c,
 SPLIT_STR(Column,',',4) AS d

CodePudding user response：

If you want to show it :

I would do it with a SELECT which through the SUBSTRING separate the columns and with its aliases rename it the name of the columns.

SELECT SUBSTRING(patient_id,1,1) AS COLUMN1,
       SUBSTRING(patient_id,3,1) AS COLUMN2, 
       SUBSTRING(patient_id,5,1) AS COLUMN3, 
       SUBSTRING(patient_id,7,1) AS COLUMN4 FROM A;

RUN EXAMPLE : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/134

RUN EXAMPLE

If you want that data in a table then do this

First create the new table

CREATE TABLE B(
  idA CHAR,
  idB CHAR,
  idC CHAR,
  idD CHAR
);

And then through the INSERT INTO table SELECT you add it

INSERT INTO B SELECT SUBSTRING(patient_id,1,1),
                     SUBSTRING(patient_id,3,1),
                     SUBSTRING(patient_id,5,1),
                     SUBSTRING(patient_id,7,1) FROM A;

RUN EXAMPLE : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/136

RUN EXAMPLE

Even if you are adding data in the table in which all the data is, it will be added as in the following example

RUN EXAMPLE

CodePudding user response：

you can also use a query like this:

WITH RECURSIVE cte_count (n,m) 
AS (
      SELECT 1,3
      UNION ALL
      SELECT n   1,n*n 
      FROM cte_count 
      WHERE n < 100
    )
SELECT TRIM( BOTH FROM SUBSTRING_INDEX( SUBSTRING_INDEX(mycol, ',', n) ,',',-1) )AS single
FROM myTable m
JOIN cte_count cnt
WHERE
cnt.n <= LENGTH(mycol) -LENGTH(REPLACE(m.mycol,',',''))  1

sample

MariaDB [test]> select * from myTable;
 ---- ---------------------------- 
| id | mycol                      |
 ---- ---------------------------- 
|  1 | a,b,c,d                    |
|  2 | Peter,Paul,Mary,john,David |
|  3 | one                        |
|  4 | one, two , three           |
 ---- ---------------------------- 
4 rows in set (0.001 sec)

MariaDB [test]> WITH RECURSIVE cte_count (n,m) 
    -> AS (
    ->       SELECT 1,3
    ->       UNION ALL
    ->       SELECT n   1,n*n 
    ->       FROM cte_count 
    ->       WHERE n < 100
    ->     )
    -> SELECT TRIM( BOTH FROM SUBSTRING_INDEX( SUBSTRING_INDEX(mycol, ',', n) ,',',-1) )AS single
    -> FROM myTable m
    -> JOIN cte_count cnt
    -> WHERE
    -> cnt.n <= LENGTH(mycol) -LENGTH(REPLACE(m.mycol,',',''))  1
    -> ;
 -------- 
| single |
 -------- 
| a      |
| b      |
| c      |
| d      |
| Peter  |
| Paul   |
| Mary   |
| john   |
| David  |
| one    |
| one    |
| two    |
| three  |
 -------- 
13 rows in set (0.004 sec)

MariaDB [test]>