Return only the main domain

[GetWebHost]

I have the following table (fields: ID and URL) with the following data:

ID ---------- URL
1 ----------- http://sitepoint.com
2 ----------- http://www.sitepoint.com
3 ----------- http://www.sitepoint.com/forum
4 ----------- http://sitepoint.com/article_here
5 ----------- http://google.com
6 ----------- http://google.com/search-results
7 ----------- http://site.com
8 ----------- http://site.com/pages/category/article
9 ----------- http://subdomain.site.com

How do I query so that it will only return the main domain? What I want in the results are only these:

• sitepoint.com
• google.com
• site.com

[smadeira]

try

SELECT substring_index(URL, '/',1) AS URL FROM TABLE

If it doesn't like the fields that have no '/' in it then you may need to do a case statement and check if the '/' exists and then get the substring. If the '/' doesn't exist then you just return the field.

[GetWebHost]

try

SELECT substring_index(URL, '/',1) AS URL FROM TABLE

If it doesn't like the fields that have no '/' in it then you may need to do a case statement and check if the '/' exists and then get the substring. If the '/' doesn't exist then you just return the field.

Thanks for the help but I it returns all the entries, from the example above it return 9 results. What I want is that it should only return three results as there are only three unique domains

[r937]

you have to use SUBSTRING_INDEX(url,'/',3) because of the slashes after http:

[GetWebHost]

Yeah, I already got it. Thanks for the response.

SELECT SUBSTRING_INDEX(URL,  '/', 3 ) AS URL,
COUNT(SUBSTRING_INDEX( URL,  '/', 3 )) AS URLCount
FROM THE_TABLE GROUP BY URL ORDER BY URLCount DESC