We help IT Professionals succeed at work.

SQL Query to just extract domain name

tbonehwd
tbonehwd asked
on
Medium Priority
764 Views
Last Modified: 2012-11-06
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?
Comment
Watch Question

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 AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
do a substring and remove the last 4 characters.

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

Commented:
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??

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

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

Author

Commented:
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

Commented:
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))
If you don't have http:// and at least one / afterwards, this will fail



select substring(URL, charindex('//',URL)+2, charindex('/', URL, charindex('//',URL)+2)-(charindex('//',URL)+2))
from #YourTable


Query with data sample:
create table #YourTable (URL nvarchar(1000))
go
insert into #YourTable (URL) values('http://www.chickensoupforthesoul.com/cs.asp?cid=newbooks')
insert into #YourTable (URL) values('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')
insert into #YourTable (URL) values('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')
insert into #YourTable (URL) values('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')
go
 
 
select substring(URL, charindex('//',URL)+2, charindex('/', URL, charindex('//',URL)+2)-(charindex('//',URL)+2))
from #YourTable
go
drop table #yourtable

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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))
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.