VinnyGuitara VinnyGuitara - 2 months ago 18
SQL Question

Get substring between second and fourth slash

I have a string 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


The strings will not always have a finite amount of slashes. For example you could have:

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


The data will always have:

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


It may have more than two levels though.

I have searched the forum but can't find anything specific.

Thanks

Answer

Probably not the best way, but this will get 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)
Comments