SQL Query reg.ex. matching

Question

i have a little problem figuring out what request (MySQL) would suit this requirements:

Table1

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

Table2

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

To select result values as

Result Set

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

Thanks in advance.

Answer 1

You could use MySQL's regexp:

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

Funny things will occur if a name matches more than one regex in Table2.