Link to home
Create AccountLog in
Avatar of raterus
raterusFlag for United States of America

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?
Avatar of Anuj
Anuj
Flag of India image

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

Avatar of 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!
Avatar of raterus

ASKER

Got It!

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

Open in new window

Avatar of 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
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of raterus

ASKER

Much cleaner, I would never had guess to use PARSENAME as a string function, but it works the same here.
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.