Fetch Element At Particular Position

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_

Solution 1:

DECLARE@pathVARCHAR(100)='/Directory_A/Directory_B/Directory_C/Folder1/Folder2'SELECTSUBSTRING( @path,LEN(@path)-LEN(SUBSTRING(@path,PATINDEX('%folder%',@path), 100))-1, 1)

Solution 2:

SQL Server doesn't have very good string processing capabilities. But, with some effort, you can do what you want.

The key idea is that the last "Directory_" is the first "_yrotceriD" in the reversed string. Then some lopping and adjusting gets what you want:

select v.*, left(v.dir, charindex('/', v.dir) - 1) as dirname
from (values ('/Directory_A/Directory_B/Directory_C/Folder1/Folder2') ) t(path) cross apply
     (values (stuff(t.path, 1, len(t.path) - charindex(reverse('Directory_'), reverse(t.path)) + 1, ''))) v(dir)

Solution 3:

Have you tried the select rightor left? If it is a string I think it is doable. Also there is something in there you will have to play around.