MySQL cross table regular expression match
I have a web application and I am working on engine that analyzes referals.
Now I have table with p开发者_运维百科ageviews along with referes that looks 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 sources definitions like:
source regex
------------------------------------------------------------
Google ^https?:\/\/[^\/]*google\.([a-z]{2,4})(\/.*)?$
Yahoo ^https?:\/\/[^\/]*yahoo\.com(\/.*)?$
What I want is third table created by joinin these two:
pv_id source
------------------------------------------------------------
5531854534 Google
8161876343 Google
8468434831 Yahoo
How to join these tables with regular expression?
UPDATE:
Changed last part of regex from (\/.*|)
to (\/.*)?
.
Try this:
select t1.pv_id, t2.source
from table1 t1
inner join table2 t2 on (t1.referer regexp t2.regex)
MySQL:
SELECT a.pv_id, b.source
FROM a, b
WHERE a.referer REGEXP b.regex
精彩评论