Get a substring between the second and fourth slashes

Question

I have a line that looks like this:

Y:\Data\apples\oranges\Scott\notes 

I need a column that looks like this:

 apples\oranges 

This is what I have so far and it does not work:

 SELECT SUBSTRING( [Group], CHARINDEX('\', [Group]) + 1, LEN([Group]) - CHARINDEX('\', [Group]) - CHARINDEX('\', REVERSE([Group])) ) from datamap.finaltest 

Lines will not always have a finite number of slashes. For example, you could:

 Y:\Data\Apples\bananas Y:\Apples\Pears\oranges\peanuts 

Data will always have:

 drive letter + '\' + '1st level folder' + '\' + 'Second level folder' 

It can have more than two levels.

I searched the forum but did not find anything specific.

thanks

Answer 1

This is probably not the best way, but it will lead you there.

 DECLARE @string varchar(255) = 'Y:\data\apples\oranges\Scott\notes' SELECT LEFT(RIGHT(@string,LEN(@string)-CHARINDEX('\', @string, CHARINDEX('\', @string,1) + 1)),CHARINDEX('\', RIGHT(@string,LEN(@string)-CHARINDEX('\', @string, CHARINDEX('\', @string,1) + 1)), CHARINDEX('\',RIGHT(@string,LEN(@string)-CHARINDEX('\', @string, CHARINDEX('\', @string,1) + 1)),1)+1)-1) 

Answer 2

Here is a way to use recursive CHARINDEX

 declare @var varchar(4000) = 'Y:\Data\apples\oranges\Scott\notes' declare @firstSlash int = (select CHARINDEX('\',@var,CHARINDEX('\',@var) + 1)) declare @fourthSlash int = (select CHARINDEX('\',@var,CHARINDEX('\',@var,CHARINDEX('\',@var,CHARINDEX('\',@var) + 1)+1)+1)) select SUBSTRING(@var,@firstSlash + 1,@fourthSlash - @firstSlash - 1) 

Or, for your data table ...

 select SUBSTRING([Group],CHARINDEX('\',[Group],CHARINDEX('\',[Group]) + 1) + 1,CHARINDEX('\',[Group],CHARINDEX('\',[Group],CHARINDEX('\',[Group],CHARINDEX('\',[Group]) + 1)+1)+1) - CHARINDEX('\',[Group],CHARINDEX('\',[Group]) + 1) - 1) 

Answer 3

A glaring approach by converting your input to XML and accepting node values ​​and re-concatenating the nodes you want in the output

 ;WITH MyTempData AS ( SELECT Convert(xml,'<n>'+Replace('Y:\Data\Apples','\','</n><n>')+'</n>') XMLString ) SELECT COALESCE(XMLString.value('(/n[3])', 'varchar(20)'),'') + '\' + COALESCE(XMLString.value('(/n[4])', 'varchar(20)'),'') MyFinalOutput FROM MyTempData 

Answer 4

If this is what you need to do frequently or is subject to change, it may be useful to implement a function that will make your code more readable / supported:

 SELECT SUBSTRING(@t, dbo.CHARINDEX2('\', @t, 2) + 1, dbo.CHARINDEX2('\', @t, 3)); 

Using this function, "find the nth place": http://www.sqlservercentral.com/scripts/Miscellaneous/30497/