我尝试了这个查询
DECLARE @AdvancedSearchSelectedDropdownName TABLE (
SelectedIds VARCHAR(2048),
AdvanceSearchOptionTypeId INT
)
INSERT INTO @AdvancedSearchSelectedDropdownName
VALUES ('4_0,5_1,6_2,7_3', 23),
('62_3', 21), ('2_4', 23)
DECLARE @selectedIds VARCHAR(MAX) = '';
SELECT @selectedIds +=
CASE WHEN SelectedIds IS NULL
THEN @selectedIds + ISNULL(SelectedIds + ',', '')
WHEN SelectedIds IS NOT NULL
THEN SUBSTRING(SelectedIds, 0, CHARINDEX('_', SelectedIds, 0)) + ','
END
FROM @AdvancedSearchSelectedDropdownName WHERE advanceSearchOptionTypeId = 23
SELECT @selectedIds
电流输出：4,2
所需输出：4,5,6,7,2
我们在SelectedIds列中可能有 n 个逗号分隔的值。
你可以走这条路：
WITH Casted AS
(
SELECT *
,CAST('<x><y>' + REPLACE(REPLACE(SelectedIds,'_','</y><y>'),',','</y></x><x><y>') + '</y></x>' AS XML) SplittedToXml
FROM @AdvancedSearchSelectedDropdownName
)
SELECT *
FROM Casted;
这将以以下形式返回您的数据：
<x>
<y>4</y>
<y>0</y>
</x>
<x>
<y>5</y>
<y>1</y>
</x>
<x>
<y>6</y>
<y>2</y>
</x>
<x>
<y>7</y>
<y>3</y>
</x>
现在我们可以抓取所有的 x 和第一个 y：
WITH Casted AS
(
SELECT *
,CAST('<x><y>' + REPLACE(REPLACE(SelectedIds,'_','</y><y>'),',','</y></x><x><y>') + '</y></x>' AS XML) SplittedToXml
FROM @AdvancedSearchSelectedDropdownName
)
SELECT Casted.AdvanceSearchOptionTypeId AS TypeId
,x.value('y[1]/text()[1]','int') AS IdValue
FROM Casted
CROSS APPLY SplittedToXml.nodes('/x') A(x);
结果：
TypeId IdValue
23 4
23 5
23 6
23 7
21 62
23 2
提示：不要存储逗号分隔的值！
以这种格式存储数据是一个非常糟糕的主意。您可以使用通用格式(如我的 XML(来存储此表或相关端表的结构。但这样的结构往往会变成脖子上真正的疼痛......
经过一番重新思考。 也许更直接一些。
现在，如果您有有限数量的_N
例
;with cte as (
Select *
,RN = Row_Number() over(Order by (Select NULL))
From @AdvancedSearchSelectedDropdownName A
)
Select AdvanceSearchOptionTypeId
,IDs = replace(
replace(
replace(
replace(
replace(
stuff((Select ',' +SelectedIds From cte Where AdvanceSearchOptionTypeId=A.AdvanceSearchOptionTypeId Order by RN For XML Path ('')),1,1,'')
,'_0','')
,'_1','')
,'_2','')
,'_3','')
,'_4','')
From cte A
Group By AdvanceSearchOptionTypeId
返回
AdvanceSearchOptionTypeId IDs
21 62
23 4,5,6,7,2
如果对帮助程序函数感兴趣。
厌倦了提取字符串(左，右，字符索引，patindex等(我修改了拆分/解析函数以接受两个非类似的分隔符。 在这种情况下，,和_.
例
;with cte as (
Select A.AdvanceSearchOptionTypeId
,B.*
,RN = Row_Number() over(Order by (Select NULL))
From @AdvancedSearchSelectedDropdownName A
Cross Apply [dbo].[tvf-Str-Extract](','+A.SelectedIds,',','_') B
)
Select AdvanceSearchOptionTypeId
,IDs = stuff((Select ',' +RetVal From cte Where AdvanceSearchOptionTypeId=A.AdvanceSearchOptionTypeId Order by RN,RetVal For XML Path ('')),1,1,'')
From cte A
Group By AdvanceSearchOptionTypeId
返回
AdvanceSearchOptionTypeId IDs
21 62
23 4,5,6,7,2
TVF，如果有兴趣
CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By N)
,RetPos = N
,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
From (
Select *,RetVal = Substring(@String, N, L)
From cte4
) A
Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/
Disclaimer.As 每个
范式，您不应该在单个单元格中存储多个值。我建议您避免以这种方式存储。
方法仍然是：创建一个UDF函数，将逗号分隔的列表分隔为表值变量。下面的代码我没有测试过。但是，它给出了如何处理这个问题的想法。
请参阅 CSV 到表方法
Declare @selectedIds varchar(max) = '';
SET @selectedIds = SELECT STUFF
(SELECT ','+ (SUBSTRING(c.value, 0, CHARINDEX('_', c.value, 0))
FROM @AdvancedSearchSelectedDropdownName AS tv
CROSS APPLY dbo.udfForCSVToList(SelectedIds) AS c
WHERE advanceSearchOptionTypeId = 23
FOR XML PATH('')),1,2,'');
SELECT @selectedIds