GROUP domain from url in MySql

Question

I have a large database that contains many urls, there are many domains repeating and i;m trying to get only the domain. eg:

http://example.com/someurl.html
http://example.com/someurl_on_the_same_domain.html
http://example.net/myurl.php
http://example.org/anotherurl.php

and i want to get only domains, eg:

http://example.com
http://example.net
http://example.org

My query is:

SELECT id, site FROM table GROUP BY site ORDER BY id DESC LIMIT 50

I need to use regex i think but i'm not mysql guru.

gkrogers · Accepted Answer · 2010-08-29 18:09:27Z

11

SELECT
    SUBSTR(site, 1 , LOCATE('/', site, 8)-1)
        as OnlyDomain
    FROM table
    GROUP BY OnlyDomain
    ORDER BY id DESC LIMIT 50

[EDIT] : After OP request, here's the updated answer that will show correct results even if domain names does not have trailing slashes:

SELECT
    SUBSTR(site, 1 , IF(LOCATE('/', site, 8), LOCATE('/', site, 8)-1, LENGTH(site)))
        as OnlyDomain
    FROM tablename
    GROUP BY OnlyDomain
    ORDER BY id DESC LIMIT 50

edited Aug 29, 2010 at 18:09

gkrogers

8,3963 gold badges31 silver badges36 bronze badges

answered Aug 28, 2010 at 13:35

shamittomar

46.8k12 gold badges77 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eben Over a year ago

thx, it seems to be working but when a url only contains domain name (google.com and not google.com/url.html) it doesn't pulling it

jimbo2087 Over a year ago

The edited code works a treat, still reasonably fast as well and saves storing the value separately.

Dennis de Jong · Accepted Answer · 2011-05-13 09:58:20Z

6

SELECT 
COUNT(*) AS nCount,
SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(site,'http://',''),'https://',''),'www.',''),'/',1) AS sDomain 
FROM tbl_table
GROUP BY sDomain 
ORDER BY 1 DESC

Addon after JQman sollution with also the www. replaced and the group by + count

answered May 13, 2011 at 9:58

Dennis de Jong

611 silver badge1 bronze badge

Comments

zevra0 · Accepted Answer · 2010-08-28 13:43:57Z

1

You can use string replacement. Assuming that the "site" column contains the url:

select id, 
substr(site,1,locate('/', site ,locate('//',site)+2)-1)
from table 
group by site
order by id
desc limit 50;

Be careful to make sure that multiple slashes are encoded, eg:

http://example.com/somethingelse/someurl.html

answered Aug 28, 2010 at 13:43

zevra0

2711 silver badge5 bronze badges

Comments

eben · Accepted Answer · 2010-08-28 20:27:59Z

1

SELECT id,
       SUBSTRING_INDEX(REPLACE(REPLACE(site,'http://',''),'https://',''),'/',1) as domain 
       FROM table
       GROUP BY domain 
       ORDER BY id DESC 
       LIMIT 50

That was working for me, if anybody needs it.

answered Aug 28, 2010 at 20:27

eben

2293 silver badges15 bronze badges

1 Comment

Patrick Savalle Over a year ago

THis one actually works best. I found it here too: sitepoint.com/forums/…

Ned Batchelder · Accepted Answer · 2010-08-28 13:35:13Z

0

You can select the domains with:

select left(site,6+locate('/',substring(site,8)))

answered Aug 28, 2010 at 13:35

Ned Batchelder

378k77 gold badges583 silver badges675 bronze badges

1 Comment

Ned Batchelder Over a year ago

If you need it to work on 'example.com', you should put an example like that in the question. Looks like you've already solved the problem though.

Collectives™ on Stack Overflow

GROUP domain from url in MySql

5 Answers 5

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related