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.

do you have 4 color in concatenated field or may be more or less? — Mobasher Fasihy, Nov 05 '14 at 04:10
there can be 4 or less colors for each id, the max number of color an id has is 4 — PMa, Nov 05 '14 at 04:32

Mobasher Fasihy · Accepted Answer · 2014-11-06T04:09:39.087

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

When I test this on my data set, this is what happened: for the ones have only 1 color --> first and third columns are filled with same value, 2nd column null; for the ones that have 2 colors --> first column filled with first color, 2nd column null, 3rd col filled with 2nd color; for the ones that have 3 colors, worked correctly. I will add a fiddle link shortly to replicate. — PMa, Nov 06 '14 at 01:39
@PerriMa i modified the query to bring the result as you want. — Mobasher Fasihy, Nov 06 '14 at 04:14

MySQL - Split String

1 Answers1

Linked