Written by Sean Behan on Sat Nov 23rd 2013

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

Tagged with..
#Postgres #SQL #Queries #URLS #Text Manipulation #Databases

Just finishing up brewing up some fresh ground comments...