使用正则表达式将一列拆分为多列 [英] Using regular expressions to split one column into multiple columns

问题描述

我有一张表格和一些数据:

I have a table and some data:

-- Table
CREATE TABLE IF NOT EXISTS `myTable` (
  `myColumn` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data
INSERT INTO `myTable` (`myColumn`) VALUES
('AAA BBB CCC'),
('AA BB CCCC'),
('BBB CC AAAA'),
('C AAA BBB'),
('CCC AA BBB');

我想运行一个返回 3 列的 SELECT 查询.

I would like to run a SELECT query that return 3 columns.

列应该被称为A、B和C.

结果应该类似于:

+------+-----+------+
| A    | B   | C    |
+------+-----+------+
| AAA  | BBB | CCC  |
| AA   | BB  | CCCC |
| AAAA | BBB | CC   |
| AAA  | BBB | C    |
| AA   | BBB | CCC  |
+------+-----+------+

我有三个正则表达式，通过使用它们，可以匹配所有数据:

I have three regular expressions which, by using them all, can match all data:

^([A]+) ([B]+) ([C]+)$
^([B]+) ([C]+) ([A]+)$
^([C]+) ([A]+) ([B]+)$

是否可以使用这些正则表达式来产生这样的结果?

Is it possible to use these regular expressions to produce such a result?

如果是这样，即使只是问题的一个子集，也希望能举个例子.

If so, would appreciate an example, even if only for a subset of the problem.

推荐答案

在 MySQL 中使用正则表达式无法做到这一点.不幸的是，MySQL 仅支持作为布尔条件的正则表达式(特别是在 where 子句中)，而不是提取或更改字符串的内容.

This can't be done using regexes in MySQL. Unfortunately, MySQL support regexes only as a boolean condition (notably in where clauses), but not to extract nor alter the content of a string.

但是，您可以使用 substring_index.以下是您的场景的示例用法.

You may however achieve what you described entirely from a MySQL query, using substring_index. Here is an example usage for your scenario.

SELECT substring_index(substring_index(myColumn, ' ', 1), ' ', -1) AS `A`, 
       substring_index(substring_index(myColumn, ' ', 2), ' ', -1) AS `B`,
       substring_index(                myColumn         , ' ', -1) AS `C`
  FROM ...  

或者，如果您必须绝对使用正则表达式，那么您可以将 MySQL 的输出通过管道传输到某个正则表达式引擎.询问我是否需要有关此策略的更多信息.

Alternatively, if you must absolutely use regexes, then you might pipe MySQL's output to some regex engine. Ask me if you need more info about this strategy.

这篇关于使用正则表达式将一列拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！