extract host-name in mySQL

[philippo]

is there anything similar to that nice PHP-function parse_url() in mySQL. I'd like to extract the host-name of an URL directly in a SELECT statement.

thanx
Philippo

[philippo]

What I'm trying to do is to get the top10 referers out of a mySQL table (mylogs) that contains a row with full-url referers.

I tried it like this:

SELECT SUBSTRING_INDEX(TRIM('http://' FROM referer),'/',1) AS ref, COUNT(SUBSTRING_INDEX(TRIM('http://' FROM referer),'/',1)) AS hits FROM mylogs WHERE referer<>'' GROUP by ref ORDER by hits DESC;

isn't there any easier way to get such a result?

anyway, general question:
way can't I call COUNT(ref) after I already defined ref as:
SUBSTRING_INDEX(TRIM('http://' FROM referer),'/',1) AS ref

why do I need to enter the whole thing again?
??

thanx a lot!
Philippo

[[deleted]]

First, no MySQL does not have any functions that let you do specific things to URLs.

Second, What is the full query you tried to use count(ref) with, and what was the mysql error?