Extract Domain Names From Links in Text with Postgres and a Single SQL Query
This query and pattern will return urls in text all within a single SQL query.
select substring(column_name from '.*://([^/]*)') as domain_name from table_name;
And here it is in a larger query, say for retrieving page view counts for referrers.
select distinct(host), count(host)
from
(
select substring(referrer from '.*://([^/]*)') as host from hits
) t1
where t1.host != ''
group by host
order by count desc
;
-- host | count
------------------------------------------------+-------
-- google.com | 4132
-- google.co.uk | 1043
-- google.ca | 399
-- bing.com | 382
-- search.yahoo.com | 335
-- google.com.au | 257
-- google.ie | 71
-- facebook.com | 52
-- google.co.nz | 45
-- google.co.in | 42
Thank you!
How would I change the regex to remove "www." from hosts as well?
With regexp_replace, the "^www." argument says to replace only if the string starts w/ the www.
Thank you Sean! You've been a big help!
Have a nice day :)
I needed to come up with something that captured the top and second level domains and thought it would probably be good to share.
regexp_replace(substring(referrer from '.://([^/])'), '^.*?([A-z0-9-]+.[^.]+$)', '\1')
This isn't perfect for all things. It fails one URLs like site.co.uk returning only co.uk . If the port number is in the URL then it will include that. But this was good enough for me.
Thanks!