REGEXP help

[rivka]

I have a list of referers stored in a table. I want to be able to pull out the referrers grouped by the referrer, with a total of hits for that referrer. BUT, I want to limit the referrer to only the base URL (so, nothing like http://www.asite.com/blah.php?blah=1&blargh=2 -- I only want [url]http://www.asite.com)[/url].

But I'm having trouble with the regular expression side of it:

SELECT COUNT(trackingID) AS numreferers, sReferer FROM tracking GROUP BY sReferer REGEXP '??????' ORDER BY numreferers DESC

Any suggestions of what to put in there that might work?

[ultraslacker]

I don't think it can be done with the mysql regexp support. The following should work, but it's a bit messy. I'm assuming that all referers begin with 'http://'

SELECT COUNT(trackingID) AS numreferers, 
concat('http://',substring(sReferer,8, position('/' in trim(LEADING 'http://' from sReferer))-1)) AS short_sReferer 
FROM tracking 
GROUP BY short_sReferer 
ORDER BY numreferers DESC 

[rivka]

After some asking around, I found a way to do it that works:

SELECT COUNT(trackingID) AS numreferers, left(sReferer, locate('/', sReferer, 8)) as base_url FROM tracking GROUP BY base_url ORDER BY numreferers DESC

Seems to work well enough, at any rate 🙂

[ultraslacker]

That's much better. Thanks for replying with that solution!