MySQL 正则表达式位于字符串中间

Question

我希望在字符串中间使用正则表达式匹配来运行 SQL 查询。

例如，我有一个字符串

状态从“需要操作”更改为“已完成”

我想搜索列 = 状态从 {REGEX} 更改为已完成

这可能吗？我不太了解正则表达式，所以正在寻求帮助

Answer 1

从您的要求和评论来看，LIKE 运算符应该非常接近您的需要:

SELECT *
FROM mytable
WHERE mycolumn LIKE 'Status Changed from % to Completed'

这将允许任何字符序列作为先前的状态。

<小时/>

另外，如果您事先知道可能的状态值列表，则可以使用正则表达式使过滤更加准确:

SELECT *
FROM mytable
WHERE mycolumn REGEXP 'Status Changed from (Needs Action)|(Open) to Completed'

REGEXP operator检查列中的值是否与给定的正则表达式匹配。竖线 (|) 代表交替运算符(至少有一个值必须匹配)。

<小时/>

<强> Demo on DB Fiddle

create table mytable (
  mycolumn varchar(500)
);

insert into mytable values 
    ('Status Changed from Needs Action to Completed'),
    ('Status Changed from Open to Completed'),
    ('Not a relevant record');

<小时/>

查询#1

SELECT *
FROM mytable
WHERE mycolumn LIKE 'Status Changed from % to Completed';

| mycolumn                                      |
| --------------------------------------------- |
| Status Changed from Needs Action to Completed |
| Status Changed from Open to Completed         |

<小时/>

查询#2

SELECT *
FROM mytable
WHERE mycolumn REGEXP 'Status Changed from (Needs Action)|(Open) to Completed';

| mycolumn                                      |
| --------------------------------------------- |
| Status Changed from Needs Action to Completed |
| Status Changed from Open to Completed         |