VocabReminder
English dictionary with notifications so you won't forget what you're studying!
Click here for more info about the app
VocabQuiz
The app that quizzes and scores you on your vocabulary!
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