?
Solved

SQL Query to just extract domain name

Posted on 2009-02-09
9
Medium Priority
?
715 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?
0
Comment
Question by:tbonehwd
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23593334
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23593338
do a substring and remove the last 4 characters.

substring(<field>, 0, len(<field) - 4)
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 25

Expert Comment

by:reb73
ID: 23593355
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??
0
 
LVL 5

Expert Comment

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

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

Author Comment

by:tbonehwd
ID: 23593591
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

0
 
LVL 25

Expert Comment

by:reb73
ID: 23593665
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))
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23593701
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

0
 
LVL 25

Expert Comment

by:reb73
ID: 23593811
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))
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question