分隔符上的SQL拆分字符串并设置为新的列。

There has been questions related to this asked, but I have not gotten the solution I am need of.

有人问过这个问题，但是我还没有得到我需要的答案。

The string(s) I am trying to split up look like this:

我想要分割的字符串是这样的:

/Dev/act/billing

或

/ST/recManage/prod/form

The issue I have is the first '/' giving me problems when I try and do things with LEFT/RIGHT/SUBSTRING/CHARINDEX. It messes up counts and stopping at the delimiter. Also, it is important to note that the number of delimiters changes. So I want to find a way to split it up so I can get every possible substring.

我遇到的问题是当我尝试用左/右/子字符串/CHARINDEX做事情时，第一个'/'给我带来的问题。它打乱计数并在分隔符处停止。此外，重要的是要注意分隔符的数量会发生变化。我想找到一种分割的方法这样我就能得到所有可能的子字符串。

RIGHT(c3.Path,CHARINDEX('/', REVERSE(c3.Path))-1) AS LastPath

This has gotten me the last part of the string. I have messed with other things ilke:

这是弦的最后一部分。我和其他事情搞混了:

SUBSTRING(c3.Path,CHARINDEX('/',c3.Path,(CHARINDEX('/',c3.Path)+1))+1,len(c3.Path)),

This gets everything after the second '/'

在第二个'/'之后

I have also messed with XML and

我还把XML和

SET @delimiter='/' 
;WITH CTE AS

(SELECT CAST('<M>' + REPLACE([Path], @delimiter , '</M><M>') + '</M>' AS XML) 
        AS [Type XML]


FROM  [Rpts].[dbo].[Cata] 
)
, 

CTE2 as (Select [Type XML].value('/M[2]', 'varchar(50)') As FirstPath from CTE)

Then doing: CTE2.FirstPath to get the result. But this then gives NULL

然后做:CTE2。获取结果的FirstPath。但这就得到了零

I am not on SQL 2016 so I cannot use SPLIT_STRING.

我没有使用SQL 2016，所以不能使用SPLIT_STRING。

Thank you

谢谢你！

2 个解决方案

#1

Try this:

试试这个:

DECLARE @mockup TABLE(ID INT IDENTITY,YourString VARCHAR(MAX));
INSERT INTO @mockup VALUES('/Dev/act/billing'),('/ST/recManage/prod/form');

SELECT m.ID
      ,B.part.value(N'text()[1]',N'nvarchar(max)')
FROM @mockup AS m
OUTER APPLY(SELECT CAST('<x>' + REPLACE(m.YourString,'/','</x><x>')  + '</x>' AS XML)) AS A(Casted)
OUTER APPLY A.Casted.nodes(N'/x[text()]') AS B(part);

This approach is save as long as you do not have forbidden characters in your string (namely <, > and &). If you need this, just call in, it is possible to solve.

只要您的字符串中没有禁用字符(即<、>和&)，就可以保存此方法。如果你需要这个，只要打个电话就可以了。

Using .nodes() with the XQuery predicat [text()] will ommit all rows with no value...

使用.nodes()和XQuery谓词[text()]将删除所有没有值的行……

The result

结果

ID  Part
---------
1   Dev
1   act
1   billing
2   ST
2   recManage
2   prod
2   form

#2

Without going dynamic, and if you have a limited (or max) number of columns, perhaps something like this:

不需要动态，如果你有一个有限的(或最大的)列数，也许像这样:

The replace() in the Cross Apply assumes the strings begins with a '/'

交叉应用中的replace()假定字符串以'/'开头

(Easy to expand or contract ... the pattern is pretty clear)

(易于扩展或收缩……模式很清楚)

Example

例子

Declare @YourTable table (ID int,[Path] varchar(max))
Insert Into @YourTable values
 (1,'/Dev/act/billing')
,(2,'/ST/recManage/prod/form')

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                      ,Pos5 = xDim.value('/x[5]','varchar(max)')
                      ,Pos6 = xDim.value('/x[6]','varchar(max)')
                      ,Pos7 = xDim.value('/x[7]','varchar(max)')
                      ,Pos8 = xDim.value('/x[8]','varchar(max)')
                      ,Pos9 = xDim.value('/x[9]','varchar(max)')
                From  (Select Cast('<x>' + replace(substring(A.Path,2,len(A.Path)),'/','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

EDIT - Slightly Simplified Version

编辑-稍微简化的版本

Notice the staggard Pos1 and /x[2]

注意staggard Pos1和/x[2]

Select A.ID
      ,Pos1 = xDim.value('/x[2]','varchar(max)')
      ,Pos2 = xDim.value('/x[3]','varchar(max)')
      ,Pos3 = xDim.value('/x[4]','varchar(max)')
      ,Pos4 = xDim.value('/x[5]','varchar(max)')
      ,Pos5 = xDim.value('/x[6]','varchar(max)')
 From @YourTable A
 Cross Apply ( Select Cast('<x>' + replace(A.Path,'/','</x><x>')+'</x>' as xml) ) B (xDim)

#1