Trimming extra details after third character instance

[dawgfather]

I have some SQL tables that has data that includes (but might not) a string of characters that has multiple forward slashes. Think of them as similar to folders and subfolders. I am trying to determine a way to trim off the excess after the third "/" - if there is a third - and leave the base details. Essentially I want to see data two folder levels down, but no further. Sample data would include something like the below and then what I am trying to get to below that.

Sample data:

Servername0

Servername1:/vol

Servername2:/vol/folder1/folder2/folder3

Servername2:/vol/folder1/folder3/folder4

Servername2:/vol/folder1/folder5

Servername2:/vol/folder6/folder7/folder8/folder9

Data looking for:

Servername0

Servername1:/vol

Servername2:/vol/folder1

Servername2:/vol/folder1

Servername2:/vol/folder1

Servername2:/vol/folder6

Anyone have some fancy coding to accomplish this?

I can do some CHARSET stuff, but then the lines without a "/" get messed up. I can do it all in Excel by doing text to columns and then concatenating selected columns back together (excluding extra columns), but that is overlay painful.

[sunny_talwar]

May be this in QlikView Script:

Table:

LOAD *,

  SubField(SampleData, '/', 1) & If(Len(KeepChar(SampleData, '/')) >= 1, '/') & SubField(SampleData, '/', 2) & If(Len(KeepChar(SampleData, '/')) >= 2, '/') & SubField(SampleData, '/', 3) as Solution;

LOAD * Inline [

SampleData

Servername0

Servername1:/vol

Servername2:/vol/folder1/folder2/folder3

Servername2:/vol/folder1/folder3/folder4

Servername2:/vol/folder1/folder5

Servername2:/vol/folder6/folder7/folder8/folder9

];

[swuehl · Accepted solution]

Or maybe

LOAD * ,

Left(Sample, If(Index(Sample,'/',3), Index(Sample,'/',3)-1,Len(Sample))) as Result

INLINE [

Sample

Servername0

Servername1:/vol

Servername2:/vol/folder1/folder2/folder3

Servername2:/vol/folder1/folder3/folder4

Servername2:/vol/folder1/folder5

Servername2:/vol/folder6/folder7/folder8/folder9

];

[dawgfather]

Worked like a charm! Thank you very much.