SQL How to extract Middle Characters in a string-CodePudding

I have a string that looks like this. I need to extract after the third '/', or to pull out the section called 'Reports' from this string.

/WM/Operational/Reports/ReportName

I have tried the following:

This gives me the last part or the ReportName

= RIGHT(col,charindex('/',reverse(col),1)-1)

this removes the leading characters after the 3rd '/' character (number of characters are different than above string to hide details)

= RIGHT(col, LEN(col) - 31)

How do I combine this together to remove the first part after the 3rd '/' and remove the reportname?

CodePudding user response：

Assuming the format of your example string has no more than 4 parts you could make use of parsename here:

declare @col varchar(50) = '/WM/Operational/Reports/ReportName';

select ParseName(Replace(stuff(@col, 1, 1, ''), '/', '.'), 2);