作为变量传递给存储过程的字符串如下:
'10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100'
我正在寻找一种将其解析为的快速方法:
'10273955|10142823|10664263|10134335|10046639|10334724|10334725'
SQL服务器是2016年
作为变量传递给存储过程的字符串如下:
'10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100'
我正在寻找一种将其解析为的快速方法:
'10273955|10142823|10664263|10134335|10046639|10334724|10334725'
SQL服务器是2016年
我的建议是:
一个模型表来模拟您的问题
DECLARE @tbl TABLE(ID INT IDENTITY,YourString VARCHAR(250));
INSERT INTO @tbl VALUES('10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100');
-查询
SELECT t.ID,t.YourString,A.CastedToXml,REPLACE(A.CastedToXml.query('data(/x/y[1])').value('.','varchar(150)'),' ','|')
FROM @tbl t
CROSS APPLY(SELECT CAST('<x><y>' + REPLACE(REPLACE(t.YourString,'|','</y></x><x><y>'),'=','</y><y>') + '</y></x>' AS XML)) A(CastedToXml);
结果
10273955|10142823|10664263|10134335|10046639|10334724|10334725
简而言之:
APPLY
将使用一些替代方法一次转换成双分隔的XML。看起来像这样:
<x>
<y>10273955</y>
<y>1</y>
</x>
<x>
<y>10142823</y>
<y>5</y>
</x>
<x>
<y>10664263</y>
<y>10</y>
</x>
<x>
<y>10134335</y>
<y>3</y>
</x>
<x>
<y>10046639</y>
<y>3</y>
</x>
<x>
<y>10334724</y>
<y>25</y>
</x>
<x>
<y>10334725</y>
<y>100</y>
</x>
诀窍是使用XQuery的data()
,该查询将XPath中的所有值作为空白分隔的片段返回。使用/x/y[1]
的XPath告诉引擎:选择每个<x>
和在其中找到的 first <y>
!。。 >
顺便说一句:在XML中,排序顺序是固定的。因此,返回的字符串将不会更改此顺序。
,使用DelimitedSplit8K_LEAD
和FOR XML PATH
(如2017年引入的STRING_AGG
),您可以执行此操作并保留顺序,但是真正的解决方案是停止存储定界的定界数据(是的,我确实意思是说两次定界,因为在您的表中是|
和=
定界了...
SELECT YT.YourColumn,STUFF((SELECT '|' + LEFT(DS.Item,CHARINDEX('=',DS.item)-1)
FROM dbo.DelimitedSplit8K_LEAD(YT.YourColumn,'|') DS
ORDER BY DS.ItemNumber
FOR XML PATH(''),TYPE).value('.','varchar(8000)'),1,'') AS NewColumn
FROM (VALUES('10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100'))YT(YourColumn)
但是,就像我说的,修复您的设计。您可以再次使用DelimitedSplit8K_LEAD
来做到这一点:
SELECT DS1.ItemNumber AS ID,CONVERT(int,MAX(CASE DS2.ItemNumber WHEN 1 THEN DS2.Item END)) AS LongNumber,MAX(CASE DS2.ItemNumber WHEN 2 THEN DS2.Item END)) AS ShortNumber
FROM (VALUES('10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100'))YT(YourColumn)
CROSS APPLY dbo.DelimitedSplit8K_LEAD(YT.YourColumn,'|') DS1
CROSS APPLY dbo.DelimitedSplit8K_LEAD(DS1.Item,'=') DS2
GROUP BY DS1.ItemNumber;
,
使用Ngrams8k,您可以这样做:
DECLARE @string VARCHAR(1000) =
'10273955=1|10142823=5|10664263=10|10134335=3|10046639=3|10334724=25|10334725=100';
SELECT NewString =
(
SELECT CASE SIGN(s.position) WHEN 1 THEN '' ELSE ng.token END
FROM dbo.NGrams8k(@string,1) AS ng
LEFT JOIN
(
SELECT ng.position,nxt.Pos,ln=nxt.Pos-ng.position
FROM dbo.ngrams8k(@string,1) AS ng
CROSS APPLY (VALUES(
ISNULL(NULLIF(CHARINDEX('|',@string,ng.position+1),0),LEN(@string)+1))) AS nxt(Pos)
WHERE ng.token = '='
) AS s ON ng.position BETWEEN s.position AND s.pos-1
ORDER BY ng.position ASC
FOR XML PATH('')
);
返回:
NewString
---------------------------------------------------------------------------------
10273955|10142823|10664263|10134335|10046639|10334724|10334725