解析一列包含sql server中的路径值 [英] parse a column contains a path value in sql server

问题描述

如何在 SQL Server 中解析包含 URL 路径值的列?

How I can parse a column that contains a URL path value in SQL Server?

输入

sites/System1/DocLib1/Folder1/SubFolder/File.pdf

应该返回:

Column 1 - Column 2 - Column 3- Column 4- Column 5 - Column 6
sites      System1    DocLib1   Folder1   SubFolder  File.pdf

每行的路径值不同

推荐答案

如果您有已知或最大的级别数，您可以使用一点 XML.

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

如果未知，则必须动态.

If unknown, you would have to go dynamic.

示例

Declare @yourtable table (id int,url varchar(500))
Insert Into @yourtable values
(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  (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

退货

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

这篇关于解析一列包含sql server中的路径值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！