asked on

Strip third+ level domains from a list

Hi, I'm trying to develop a query to consolidate and get just the 1st and 2nd level domains in a list.

e.g. this query will take

blah.somedomain.com
fourth.third.somedomain.com
whatever.somedomain.com
test.anotherdomain.com
fourth.test.anotherdomain.com

and simply return
somedomain.com
anotherdomain.com

Can I do this from a sql query, if so how?

Anuj

One way is ...

declare @t varchar(max) = 'blah.somedomain.com'

SELECT SUBSTRING(@t,CHARINDEX('.',@t)+1,len(@t)-CHARINDEX('.',@t)+1)

eg: SELECT SUBSTRING(Column,CHARINDEX('.',Column)+1, len(Column) - CHARINDEX('.',Column)+1)
FROM MyTable

Open in new window

raterus

ASKER

Hmm, close, but that assumes the domain only has a third/second/top syntax, so it turns "fourth.third.second.first" into "third.second.first", which isn't what I want.

Thanks for your help so far!

raterus

ASKER

Got It!

select distinct reverse(substring(reverse(hostname), 0, charindex('.', reverse(hostname), charindex('.', REVERSE(hostname))+1)))
from MyTable

Open in new window

raterus

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for raterus's comment #a38459830

for the following reason:

This is the only solution that answers the question.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

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

Start Free Trial

raterus

ASKER

Much cleaner, I would never had guess to use PARSENAME as a string function, but it works the same here.

Anthony Perkins

PARSENAME() is a string function, it is just normally used for SQL Server Objects.

Word of caution: PARSENAME() supports only 4 parts so if you have this fifth.fourth.third.somedomain.com, it will break down. So feel free if you want to withdraw the points.