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

and simply return

Can I do this from a sql query, if so how?
Avatar of Anuj
Flag of India image

One way is ...

declare @t varchar(max) = ''

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


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


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


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

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


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, it will break down.  So feel free if you want to withdraw the points.