CODEWITHSUNDEEP
sql-servert-sql
bRaNdOn
bRaNdOn

Reputation: 1082

Get Folder Name from a path column in MS SQL query

how Get folder name to a directory path in SQL

currently have this query to get the folder name of the path noticed that the @test variable returned incorrect folder name which is = "\Q" which is missing letter A and when i tried to add \ on the @test2 it got the right foldername which is QA i'd like to get a query that can accommodate these two scenerias for @test and @test2 which will return QA any ideas?

DECLARE @Test VARCHAR(MAX) = '\\asda.xxv.ww\data\tas\Test\Clients\asdas\TestFolder\QA'
DECLARE @Test2 VARCHAR(MAX) = '\\uxcvs.xcxcv.xcv\data\tas\Test\Clients\xcvcx\xcvxcv\QA\'

SELECT RIGHT(SUBSTRING(@Test, 2, LEN(@Test)-2), CHARINDEX('\', SUBSTRING(REVERSE(@Test), 2, LEN(REVERSE(@Test))-2), (CHARINDEX('\', SUBSTRING(REVERSE(@Test), 2, LEN(REVERSE(@Test))-2))))) AS DiconFilePath , 
RIGHT(SUBSTRING(@Test2, 2, LEN(@Test2)-2), CHARINDEX('\', SUBSTRING(REVERSE(@Test2), 2, LEN(REVERSE(@Test2))-2), (CHARINDEX('\', SUBSTRING(REVERSE(@Test2), 2, LEN(REVERSE(@Test2))-2))))) AS DiconFilePath2

Upvotes: 0

Views: 1034

Answers (2)

AhmedHuq
AhmedHuq

Reputation: 469

You can try the following, may need some testing to ensure works in all cases.

DECLARE @Test VARCHAR(MAX) = '\\asda.xxv.ww\data\tas\Test\Clients\asdas\TestFolder\QA'
DECLARE @Test2 VARCHAR(MAX) = '\\uxcvs.xcxcv.xcv\data\tas\Test\Clients\xcvcx\xcvxcv\QA\'

SELECT RIGHT(SUBSTRING(@Test, 2, LEN(@Test)-2), CHARINDEX('\', SUBSTRING(REVERSE(@Test), 2, LEN(REVERSE(@Test))-2), (CHARINDEX('\', SUBSTRING(REVERSE(@Test), 2, LEN(REVERSE(@Test))-2))))) AS DiconFilePath , 
RIGHT(SUBSTRING(@Test2, 2, LEN(@Test2)-2), CHARINDEX('\', SUBSTRING(REVERSE(@Test2), 2, LEN(REVERSE(@Test2))-2), (CHARINDEX('\', SUBSTRING(REVERSE(@Test2), 2, LEN(REVERSE(@Test2))-2))))) AS DiconFilePath2
-----------------------
,patindex('%\%', reverse(@Test))
,patindex('%\%', reverse(@Test2))
----------------
,case 
    when patindex('%\%', reverse(@Test)) > 1  THEN 
        reverse(LEFT(reverse(@Test), patindex('%\%', reverse(@Test)) - 1))
    else
        @Test
end
,
case
    WHEN patindex('%\%', reverse(@Test2)) = 1 and len(@Test2) > 1 then
    right(reverse(@Test2),LEN(reverse(@Test2)) - 1)
    ELSE
     @Test2
end
,case
    WHEN patindex('%\%', reverse(@Test2)) = 1 and len(@Test2) > 1 then
        reverse(substring(right(reverse(@Test2),LEN(reverse(@Test2)) - 1), 0, patindex('%\%', right(reverse(@Test2),LEN(reverse(@Test2)) - 1))))
    ELSE
        @Test2
end

Upvotes: 1

Ahmmed
Ahmmed

Reputation: 1048

Change variable @test or @test2 will return correct values-QA

select REVERSE(substring( CASE WHEN CHARINDEX ('\', REVERSE(@Test))=1 THEN RIGHT(REVERSE(@Test), LEN(@Test) - 1)   ELSE REVERSE(@Test) END,1,
CASE WHEN CHARINDEX ('\', REVERSE(@Test))=1 THEN CHARINDEX ('\', RIGHT(REVERSE(@Test), LEN(@Test) - 1)) else CHARINDEX ('\', REVERSE(@Test)) end
-1))

Upvotes: 2

Related Questions