Domain Extension Splitting Based on Dot(.)

Question

Domain Extension Splitting Based on Dot(.)

sathishkm

SSC Veteran

Points: 239
More actions
April 5, 2023 at 8:31 pm

#4169686

Hi All,
I need to extract the domain extension from the column based on dot(.) like the below

Attachments:
You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Your posted example is great but please Read'n'Heed the article at the first link in my signature line below for future posts for how to post "Readily Consumable" data to help us help you.

Also, please tell us the business reason why you needed to do this. I'm very curious about such things.

Since you're kind of new, here's one way you could have posted the data to that it's readily consumable.

 SELECT *
   INTO #YourTable
   FROM (VALUES
         ('futuredirections.org.au')
        ,('shipairlift.com')
        ,('financialenterprise.ca')
        ,('cmsf.saline.k12.il.us')
        )v(Domain)
;

Here's one way to solve you problem using that test data.

 SELECT  yt.Domain
        ,Part1 = v2.P1
        ,Part2 = ISNULL(v2.P2+v2.P1,'')
        ,Part3 = ISNULL(v2.P3+v2.P2+v2.P1,'')
        ,Part4 = ISNULL(v2.P4+v2.P3+v2.P2+v2.P1,'')
   FROM #YourTable yt
  CROSS APPLY (VALUES(SUBSTRING(yt.Domain,CHARINDEX('.',yt.Domain)+1,8000)))v1(P41)
  CROSS APPLY (VALUES
                (PARSENAME(v1.P41,4)+'.'
                ,PARSENAME(v1.P41,3)+'.'
                ,PARSENAME(v1.P41,2)+'.'
                ,PARSENAME(v1.P41,1)
                ))v2(P4,P3,P2,P1)
;

Here are the results from the code above.

If the individual parts are more than 128 characters long, we'll need to do something else. Lookup PARSENAME and see why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Nice one Jeff. That's a good looking query. It makes good use of '+' as the NULL respecting concatenation operator. My initial thought was to reverse the string and use a shedload of CHARINDEX and SUBSTRING

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Thanks, Steve. Thank you for the feedback.

I'd also considered doing the REVERSE thing and using 4 CHARINDEXes, for such a few elements, it's usually faster than a splitter and re-pivoting, etc. Then, I thought, PARSENAME takes care of all that and it's fairly easy to take grand advantage of the NULLs it produces for the missing periods to not use unused periods.

I've not tested it for performance, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Domain Extension Splitting Based on Dot(.)

Attachments: