SQL-从数组中获取特定元素 [英] SQL - Get specific element from an array

问题描述

假设我有2个看起来像数组的变量：

Suppose I have 2 variables that look like an array:

declare @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
declare @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

我需要查找 @code 是否包含 10490 （例如），如果需要，我需要在 @value 变量将是数字，因为 10490 是 @code 数组和 @value 数组的第4个元素是 Digital （请注意， @value 数组为NULL。

I need to find if @code contains 10490 (for example) and if it does, I need to find a corresponding value (by its index) in @value variable which would be Digital since 10490 is the 4th element in @code array and 4th element of @value array is Digital (note that the 2nd element of the @value array is NULL.

免责声明：
@code 数组将始终包含唯一值，例如，不可能有超过1个 10490 。
@code 数组将始终以'，'结尾
@code 和<$ c $中的元素数如果您将 @code 变量的第一个和最后一个逗号作为逗号，则c> @value 始终相同。
我无法使用函数或存储过程，因此所有操作都需要作为1个查询的一部分来完成。

Disclaimer: @code array will ALWAYS contain unique values. It's not possible to have more than 1 10490 for example. @code array will always start and end with ','. Number of elements in @code and @value will always be the same if you take 1st and last comma off the @code variable. I cannot use functions or stored procedures, so everything needs to be done as part of 1 query.

推荐答案

我想您知道，这是一个非常糟糕的设计...如果您可以更改它，那么您确实应该这样做。但这可以解决：

I think you know, that this is a very bad design... If you can change this, you really should. But this can be solved:

declare @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
declare @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

-查询会将两个字符串都转换为可拆分的XML

- query（'/ x [text（）]'）将删除空条目（前导和尾随逗号）

-（...假定在 @code 中永远不会有空条目）

-然后它将从两个

中读取派生的编号列表--最终它将在其 PartIndex

--The query will cast both strings to a splittable XML
--The query('/x[text()]') will remove empty entries (leading and trailing comma)
--(...assuming there will never be an empty entry in @code)
--Then it will read a derived numbered list from both
--finally it will join both lists on their PartIndex

WITH Casted AS
(
    SELECT CAST('<x>' + REPLACE(@code,',','</x><x>') + '</x>' AS XML).query('/x[text()]') AS CodeXml
          ,CAST('<x>' + REPLACE(@value,'~','</x><x>') + '</x>' AS XML) AS ValueXml
)
,CodeDerived AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartIndex
          ,x.value('text()[1]','nvarchar(max)') AS CodePart
    FROM Casted
    CROSS APPLY CodeXml.nodes('/x') A(x)
)
,ValueDerived AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartIndex
          ,x.value('text()[1]','nvarchar(max)') AS ValuePart
    FROM Casted
    CROSS APPLY ValueXml.nodes('/x') A(x)
)
SELECT cd.PartIndex
      ,CodePart
      ,ValuePart
FROM CodeDerived cd
INNER JOIN ValueDerived vd ON cd.PartIndex=vd.PartIndex

结果

inx     CodePart    ValuePart
1       10501       True
2       10203       NULL
3       10491       100000006
4       10490       Digital
5       10091       0
6       10253       0
7       10008       NULL
8       10020       1388.76
9       10570       Completed
10      10499       True

只需添加一个简单的 WHERE 将其减少为您需要的值。

Just add a simple WHERE to reduce this to the one value you need.

免责声明：不能保证使用 ROW_NUMBER 和 ORDER BY（SELECT NULL）会返回正确的序列，但是为了更好的机会，您需要使用SQL Server 2016+。 有关更多详细信息：请阅读此链接和此处的其他内容

Disclaimer: it is not guaranteed, that the numbering with ROW_NUMBER and ORDER BY (SELECT NULL) will ever return the correct sequence, but for a better chance you'd need SQL Server 2016+. For more details: read this link and the other contributions there

这篇关于SQL-从数组中获取特定元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！