folder name

Question

Post reply

folder name

sharonsql2013

SSCertifiable

Points: 5908
More actions
May 26, 2016 at 1:45 pm

#319042

I am trying to extract the folder name from the path which usually is after the 2nd delimiter
example
/dev/Mywork/deailsbyTeam
I need to get "Mywork"
please advice

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63434 More actions · Answer 1

pietlinden

SSC Guru

Points: 63434

May 26, 2016 at 2:03 pm

#1881679

Use DelimitedSplit8K and grab the 3rd item?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

sharonsql2013 (5/26/2016)
I am trying to extract the folder name from the path which usually is after the 2nd delimiter
example
/dev/Mywork/deailsbyTeam
I need to get "Mywork"
please advice

The problem I see is your use of "usually" above. Could you provide more clarity as to what you are looking for here?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

When you say folder name, is it the root folder or the parent folder?

Can you give several examples? Mainly to get exceptions to the rule. Don't forget to include expected results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 4

As mentioned

It could be /dev/Mywork/deailsbyWork

or /dev/MyTeam/deailsbyTeam

or /dev/MySubgroup/deailsbySubgroup

Outcome

Mywork

MyTeam

MySubgroup

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sharonsql2013 (5/26/2016)
As mentioned
It could be /dev/Mywork/deailsbyWork
or /dev/MyTeam/deailsbyTeam
or /dev/MySubgroup/deailsbySubgroup
Outcome
Mywork
MyTeam
MySubgroup

Based on the above, usually then mean always, correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Perhaps this?

with testdata as (

select

Directory

from

(values

('/dev/Mywork/deailsbyWork'),

('/dev/MyTeam/deailsbyTeam'),

('/dev/MySubgroup/deailsbySubgroup'))dt(Directory)

)

select

Directory,

substring(Directory,charindex('/',Directory,2) + 1,(charindex('/',Directory,charindex('/',Directory,2) + 1) - charindex('/',Directory,2)) - 1)

from testdata;

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 7

sharonsql2013

SSCertifiable

Points: 5908

May 26, 2016 at 3:04 pm

#1881708

Thank you Lynn

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

Alternative method using Lynn's fine test set (with the addition of a Beginninator 😀 )

😎

;WITH testdata AS (

SELECT

Directory

FROM

(VALUES

('/dev/Mywork/deailsbyWork'),

('/dev/MyTeam/deailsbyTeam'),

('/dev/MySubgroup/deailsbySubgroup'))dt(Directory)

)

,FIRST_CHOP AS

(

SELECT

TD.Directory

,SUBSTRING(TD.Directory,CHARINDEX(CHAR(47),TD.Directory,2) + 1,LEN(TD.Directory)) AS PART

FROM testdata TD

)

SELECT

FC.Directory

,SUBSTRING(FC.PART,1,CHARINDEX(CHAR(47),FC.PART,2) - 1) AS DIR_NAME

FROM FIRST_CHOP FC;

Output

Directory DIR_NAME

-------------------------------- ------------

/dev/Mywork/deailsbyWork Mywork

/dev/MyTeam/deailsbyTeam MyTeam

/dev/MySubgroup/deailsbySubgroup MySubgroup