Parse A Column Contains A Path Value In Sql Server

How I can parse a column that contains a URL path value in SQL Server? The input sites/System1/DocLib1/Folder1/SubFolder/File.pdf should return: Column 1 - Column 2 - Column 3- Co

Solution 1:

If you have a known or max number of levels, you can use a little XML.

If unknown, you would have to go dynamic.

Example

Declare@yourtabletable (id int,url varchar(500))
InsertInto@yourtablevalues
(1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

Select A.id
      ,B.*From@yourtable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (SelectCast('<x>'+ replace(url,'/','</x><x>')+'</x>'as xml) as xDim) as A 
             ) B

Returns

id  Pos1    Pos2    Pos3    Pos4    Pos5        Pos6        Pos7    Pos8    Pos9
1   sites   System1 DocLib1 Folder1 SubFolder   File.pdf    NULLNULLNULL