sql - Fetch element at particular position

Question

What is the SQL syntax to find last directory name from a file path. For example I want to fetch the name of the last Directory i.e. C

Input:

/Directory_A/Directory_B/Directory_C/Folder1/Folder2

Output:

C

Answer 1

DECLARE @path VARCHAR(100)='/Directory_A/Directory_B/Directory_C/Folder1/Folder2'

SELECT SUBSTRING( @path,LEN(@path)-LEN(SUBSTRING(@path,PATINDEX('%folder%',@path), 100))-1, 1)