VocabReminder
Easily build a great vocabulary without studying! Now on the App Store!
Click here for more info about the app

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
Tagged w/ #postgres #sql #queries #urls #text manipulation #databases