MySQL-分割字串 [英] MySQL - Split String

问题描述

我的问题类似于这篇文章: >反向GROUP_CONCAT"；在MySQL中?

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

但是，除了将group_concat反转之外，还有一种方法可以将字符串分成自己的列，如下所示:

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        |         |

我也调查了这篇文章: 如何在mysql中拆分名称字符串?

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.

推荐答案

这可能是您要查询的查询:

This may be the query you look for:

第一个表格结构:

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

结果是 first_color | second_color | third_color 绿色|红色|黑色 小提琴在此处输入链接描述

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

但是对于3种以上的颜色，每种颜色都有其自己的顺序，我认为波纹管查询是正确的.

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;

要知道可以在COLOR字段中发生的最大串联次数

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

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

然后使用循环根据表中最大颜色数进行查询的if部分. 小提琴此处

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

这篇关于MySQL-分割字串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！