asked on

SQL Query to just extract domain name

I am not sure if there is a easy way of doing this but I have a field in our database called referrer. I would like to write a query that pulls the domain name only without the extra information after the .com, .net etc... I then want to be able to group and do a count on how many came from a particular domain.... Any ideas on how to do this easily?

60MXG

http://myitforum.com/cs2/blogs/dhite/archive/2008/07/13/sql-query-get-machine-and-user-information-from-a-specified-collection.aspx

BrandonGalderisi

Are they email addresses, or web addresses? Do they have the host, domain and TLD (ex. www.google.com) or just google.com?

What about www.something.co.uk? What part would you want? "something" I would assume but parsing would be difficult if everything isn't in a consistent format.

declare @YourField nvarchar(1000)
 
set @yourfield = 'www.google.com'
select left(@YourField, len(@YourField)-charindex('.', reverse(@YourField)))
 
set @yourfield = 'google.com'
select left(@YourField, len(@YourField)-charindex('.', reverse(@YourField)))

Open in new window

Kyle Abrahams, PMP

do a substring and remove the last 4 characters.

substring(<field>, 0, len(<field) - 4)

reb73

You will have to parse the field values in terms of patterns, can you list a few sample distinct values from referrer field?
Does the referrer field contain the http:\\ prefix consistently??

sampipes

declare @url nvarchar(100)
set @url = 'http://monkey.com/page1'

select left(@url,charindex('/',replace(@url,'//','--'))-1)

tbonehwd

ASKER

What about everything between // and the first /

Here are some examples:
http://www.chickensoupforthesoul.com/cs.asp?cid=newbooks
http://books.google.com/books?id=tn7KmYcykhQC&dq=the+financial+wisom+of+ebenezer+scrooge&pg=PP1&ots=BvLUJLhT0Q&sig=z2X0X6JYbGQuT6UCp1lA6csxMZQ&prev=http://www.google.com/search?hl=en&q=The+Financial+Wisom+of+Ebenezer+Scrooge&sa=X&oi=print&ct=title&cad=one-book-with-thumbnail
http://books.google.com/books?id=tn7KmYcykhQC&dq=the+financial+wisdom+of+ebeneezer+scrooge&pg=PP1&ots=BvLUJLj_-L&sig=FuS9Xn5mOR3QmmoHonsgFCXdPZU&prev=http://www.google.com/search?hl=en&q=the+financial+wisdom+of+ebeneezer+scrooge&sa=X&oi=print&ct=title&cad=one-book-with-thumbnail
http://www.google.com/search?hl=en&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=health+communications,+inc+chicken+soup+for+the+soul+christmas+treasury&spell=1
http://www.chickensoup.com/cs.asp?cid=newbooks
http://www.google.com/search?hl=en&rlz=1T4SUNA_enUS224US224&q=hci&btnG=Search
http://by116w.bay116.mail.live.com/mail/ApplicationMain_12.1.0068.1031.aspx?culture=en-US&hash=1728709544
http://webmail.aol.com/33060/aol/en-us/Suite.aspx
http://webmail.aol.com/33060/aim/en-us/Mail/DisplayMessage.aspx
http://errorpage.comcast.net/?cat=web&con=dc&safe=on&q=healthcommunications,inc.
http://www.teenink.com/TeenInkBook/TeenInkBookstore.php
http://bl109w.blu109.mail.live.com/mail/ReadMessageLight.aspx?Aux=4|0|8CA06670A996840|&FolderID=00000000-0000-0000-0000-000000000001&InboxSortAscending=False&InboxSortBy=Date&ReadMessageId=9d2ee4b4-00a2-45c8-bdd2-4c69cbf83cbb&n=975515131
http://us.f542.mail.yahoo.com/ym/ShowLetter?MsgId=478_29476143_1104564_1363_8328_0_378843_30496_4089475478&Idx=8&YY=27600&y5beta=yes&y5beta=yes&inc=25&order=down&sort=date&pos=0&view=a&head=b&box=Inbox

reb73

An adaptation of sampipes's code -

declare @url nvarchar(100)
set @url = 'http://www.monkey.co.uk/page1'

select substring(left(@url,charindex('/',replace(@url,'//','--'))-1), ISNULL(NULLIF(patindex('%//%', @url), 0), -1) + 2, len(@url))

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

reb73

I think, this should handle all your cases -

declare @url nvarchar(100)
set @url = 'http://www.monkey.co.uk'

select substring(left(@url,ISNULL(NULLIF(charindex('/',replace(@url,'//','--')), 0)-1, len(@url))), ISNULL(NULLIF(patindex('%//%', @url), 0), -1) + 2, len(@url))