mysql - SQL 查询 reg.ex。匹配

Question

我在确定什么请求 (MySQL) 适合此要求时遇到了一些问题:

表1

+++++++++++++++++
id| name
+++++++++++++++++
1 | John Smith
2 | Eric Smith
3 | Martha Smith
4 | Smith
5 | Ronald Smith Donald
6 | Marc Fisher

表2

+++++++++++++++++++++++
regex | value1 | value2
+++++++++++++++++++++++
Smith | Mister | 356
Fisher| Sir    | 24

选择结果值作为

结果集

+++++++++++++++++++++++++++++++++++++++++++++++++++
id| name                | regex  | value1 | value2 
+++++++++++++++++++++++++++++++++++++++++++++++++++
1 | John Smith          | Mister | 356
2 | Eric Smith          | Mister | 356
3 | Martha Smith        | Mister | 356
4 | Smith               | Mister | 356
5 | Ronald Smith Donald | Mister | 356
6 | Marc Fisher         | Sir    | 24

提前致谢。

Answer 1

您可以使用 MySQL 的 regexp :

select  *
from    Table1 t1
join    Table2 t2
on      t1.name regexp t2.regex

如果一个名称与表 2 中的多个 regex 匹配，将会发生有趣的事情。