Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Query for finding all domain names in URLs

    Hi,

    I'm storing all URLs of websites visited in a week in a table and I'm struggling to write a query that would output the different domain names of the URLs stored in the table.

    My purpose in doing so is to group together all URLs belonging to the same website. Below is an example.

    Record 1: http://www.somesites.com/agdfs
    Record 2: http://www.somesites.com/ghstff

    Although the URLs are different, the main website is the same (Untitled Document) for many records which is what I'm interested in so that I can group all the records that would have Untitled Document as main site together.

    I've written a substring query using the locate function but unfortunately, the locate function will only work if I provide it with an already defined domain name, but my problem is that from the thousands of records that I have, I can't manually write all domain names. By the way, the good thing that the query that I wrote does, is that it removes the remaining characters after the last foreslash, in the example above that would be displaying Untitled Document.


    Please help me.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm pretty sure you don't want to GROUP, but rather, only ORDER

    if you need to extract the domain, you should really do that when storing the url

    extracting it after the fact is always gonna run into trouble

    e.g. distinguishing between example.com and IANA — Example domains

    e.g. deciding whether you want only co.uk or example.co.uk (huge difference)

    so do your logic before/while storing the urls, is my advice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •