MySQL regex matching crosstab

Question

I have a web application and am working on an engine that analyzes referrals.

Now I have a table with pageviews, as well as links that look something like this:

pv_id        referer
------------------------------------------------------------
5531854534   http://www.google.com/search?ie=UTF-8...
8161876343   http://google.cn/search?search=human+rights
8468434831   http://search.yahoo.com/search;_...

The second table contains source definitions, such as:

source       regex
------------------------------------------------------------
Google       ^https?:\/\/[^\/]*google\.([a-z]{2,4})(\/.*)?$
Yahoo        ^https?:\/\/[^\/]*yahoo\.com(\/.*)?$

What I want is the third table created by joinin by these two:

pv_id        source
------------------------------------------------------------
5531854534   Google
8161876343   Google
8468434831   Yahoo

How to join these regular expression tables?

UPDATE:

Changed the last part of the regular expression from (\/.*|)to (\/.*)?.

Answer 1

Try the following:

select t1.pv_id, t2.source
from table1 t1
  inner join table2 t2 on (t1.referer regexp t2.regex)

Answer 2

MySQL:

SELECT a.pv_id, b.source 
FROM a, b
WHERE a.referer REGEXP b.regex