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?

Microsoft SQL Server

Last Comment

Anthony Perkins

8/22/2022 - Mon

Anuj

10/3/2012

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

10/3/2012

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

10/3/2012

ASKER

Got It!

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

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

raterus

10/3/2012

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

10/3/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

raterus

10/3/2012

ASKER

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

Anthony Perkins

10/3/2012

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.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.