MySQL cross table regular expression match
- by Josef Sábl
I have a web application and I am working on engine that analyzes referals.
Now I have table with pageviews 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?
