MySQL:删除包含数字的子字符串

Question

我的 MySQL 表中有一个列，其中有一个由代码和标题组成的字符串，我需要将它们分成两个单独的列。

目前，只有 title 列有数据。我可以将 title 复制到 code 中，但随后我想从 title 列中删除包含数字的子字符串，并删除不的子字符串> 包含 code 列中的数字。

示例值类似于 NWAB110438 Lagoon，我希望 NWAB110438 位于 code 中，Lagoon 位于 标题。

我知道如何选择包含数字的记录，但不知道如何删除包含数字的子字符串。

编辑

我还应该补充一点，有时带有数字的子字符串将位于不带数字的子字符串之后，有时该列将仅包含单个子字符串，因此以下是所有可能的值:

• NWAB110438 泻湖
• 泻湖 NWAB110438
• 泻湖
• NWAB110438

所以看起来 substring_index 并不总是匹配正确的值。

Answer 1

使用 SUBSTRING_INDEX、INSTR、REGEXP 的解决方案，带有完整的演示。

SQL:

-- data
create table t1(title varchar(100), code varchar(100));
insert into t1 values
('NWAB110438 Lagoon', 'NWAB110438 Lagoon'),
('Lagoon NWAB110438', 'Lagoon NWAB110438'),
('Lagoon', 'Lagoon'),
('NWAB110438', 'NWAB110438');
select * from t1;

-- query wanted
UPDATE t1
SET 
    title = TRIM(CONCAT(IF((@first := SUBSTRING_INDEX(title, ' ', 1)) REGEXP '[0-9]+', '', @first), 
                        ' ', 
                        IF((@second := IF(INSTR(title, ' '), SUBSTRING_INDEX(title, ' ', -1), '')) REGEXP '[0-9]+', '', @second))),
    code = TRIM(CONCAT(IF(@first REGEXP '[0-9]+', @first, ''), 
                        ' ', 
                        IF(@second REGEXP '[0-9]+', @second, '')));
select * from t1;

输出:

mysql> select * from t1;
+-------------------+-------------------+
| title             | code              |
+-------------------+-------------------+
| NWAB110438 Lagoon | NWAB110438 Lagoon |
| Lagoon NWAB110438 | Lagoon NWAB110438 |
| Lagoon            | Lagoon            |
| NWAB110438        | NWAB110438        |
+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql> UPDATE t1
    -> SET
    ->     title = TRIM(CONCAT(IF((@first := SUBSTRING_INDEX(title, ' ', 1)) REGEXP '[0-9]+', '', @first),
    ->                         ' ',
    ->                         IF((@second := IF(INSTR(title, ' '), SUBSTRING_INDEX(title, ' ', -1), '')) REGEXP '[0-9]+', '', @second))),
    ->     code = TRIM(CONCAT(IF(@first REGEXP '[0-9]+', @first, ''),
    ->                         ' ',
    ->                         IF(@second REGEXP '[0-9]+', @second, '')));
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t1;
+--------+------------+
| title  | code       |
+--------+------------+
| Lagoon | NWAB110438 |
| Lagoon | NWAB110438 |
| Lagoon |            |
|        | NWAB110438 |
+--------+------------+
4 rows in set (0.00 sec)