MySQL - Split String

Question

My question is similar to this post: "Reverse GROUP_CONCAT" in MySQL?

However, instead of reverse the group_concat, is there a way to split out the string into its own column like this:

  id | colors1      | color 2      | color 3 | color 4
+----+-----------------------------+---------+----------
| 1  | Red          | Green        | Blue    | Black
| 2  | Orangered    | Periwinkle   | Black   |
| 3  | Orange       | Black        |         |

I also looked into this post: How to split the name string in mysql?

But I could not figure out how to get the output I need.

Answer 1

This may be the query you look for:

First table structure:

CREATE TABLE color (
  id int AUTO_INCREMENT,
  col_type varchar(255),
  PRIMARY KEY (id)
);
INSERT INTO color (col_type)
  VALUES(
         'GREEN,RED,BLACK'
);

SELECT
      SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 1), ',', -1) AS first_color,
        If(  length(col_type) - length(replace(col_type, ',', ''))>1,  
             SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 2), ',', -1) ,NULL) 
               as second_color,
             SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 3), ',', -1) AS last_color
FROM color

the result is as first_color | second_color | third_color GREEN | RED |BLACK fiddle enter link description here

But for more than 3 color and each color in its own order i think the bellow query is the right one.

SELECT
   COLOR,
   SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 1), ',', -1) AS first_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 2), ',', -1) ,NULL) 
       as second_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=2,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 3), ',', -1) ,NULL) 
       AS third_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=3,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 4), ',', -1) ,NULL) 
       AS fourth_color
FROM COLOR;

to know the max number of concatenation occurred in COLOR field you can do

select (length(COLOR) - length(replace(COLOR, ',', '')) as NumColors

Then use loop to make the if section of query according to the max number of colors in table. fiddle here