34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @T TABLE(ID INT IDENTITY,Name NVARCHAR(50))
INSERT INTO @T
( Name )
VALUES ( 'AAA^BBB^CCC-DDD^EEE^FFF-GGG^HHH^III' -- Name - nvarchar(50)
)
DECLARE @Condition VARCHAR(50)='DDD^EEE^FFF'
;WITH Cte
AS
(
SELECT a.ID,SUBSTRING(a.Name,b.number,CHARINDEX('-',a.Name+'-',b.number+1)-b.number-1) AS Condition,SeqmentNr=ROW_NUMBER()OVER(PARTITION BY a.ID ORDER BY b.number) FROM @T AS a,master.dbo.spt_values AS b WHERE b.type='P' AND b.number>=0 AND CHARINDEX('-','-'+a.Name,b.number)=b.number
)
SELECT a.*,STUFF(b.SeqmentNrs,1,1,'') AS SeqmentNrs FROM @T AS a CROSS APPLY(
SELECT ','+RTRIM(Cte.SeqmentNr) FROM Cte WHERE Cte.Condition LIKE @Condition+'%' OR @Condition LIKE Cte.Condition+'%' FOR XML PATH('')) AS b(SeqmentNrs)
/*
ID Name SeqmentNrs
1 AAA^BBB^CCC-DDD^EEE^FFF-GGG^HHH^III 2
*/
-- 前后各加一个 ^,传来的参数也加
-- 效率看看能不能达到你的要求
where charindex(^A^B^C^P^ , ^B^) > 0