拆分列表时确定第n个项目? [英] Identify nth item when splitting a list?

问题描述

我想在 cte 中提供一个 ROW_NUMBER 能够从具有给定索引的序列中选择特定项目(如数组索引器 arr[index]).当然，序列"是来自表值函数(Split)的表.

I want to provide a ROW_NUMBER in a cte be able to pick a specific item out of a sequence with a given index(like an array-indexer arr[index]). Of course the "sequence" is a table which comes from a table-valued-function(Split).

那么当索引不可用时如何创建索引?这是查询:

So how can I create an index when it's not available? This is the query:

DECLARE @string VARCHAR(100); 
SET @string='field1;field2;field3;field4;field5;field6;field7'; 
DECLARE @index INT; 
SET @index = 4; 

WITH cte 
     AS (SELECT item, 
                rn=Row_number() 
                     OVER( 
                       ORDER BY item) 
         FROM   dbo.Split(@string, ';')) 
SELECT TOP 1 item 
FROM   cte 
WHERE  rn = @index 

Row_number() OVER(ORDER BY item) 按字母顺序排序，但实际上我想按(不可用)索引排序.

Row_number() OVER(ORDER BY item) orders alphabetically but actually I want to order by a (not available) index.

请注意，这个问题出现在 我的回答上这个问题.

Note that this question arised on my answer on this so-question.

推荐答案

当然，您只需要跟踪每个项目在列表中的位置.一种方法是使用 IDENTITY 列插入拆分函数.从您参考的问题中获取我的回答:

Sure, you just need to keep track of where in the list each item appears. One way to do that is to have your split function insert with an IDENTITY column. Taking my answer from the question you reference:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS @t TABLE([Index] INT IDENTITY(1,1), Item NVARCHAR(255))
AS
BEGIN
    INSERT @t(Item) SELECT SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ORDER BY Number OPTION (MAXDOP 1);

    RETURN;
END
GO

DECLARE @x TABLE(i INT, string NVARCHAR(4000));

INSERT @x SELECT 1, N'field1;field2;field3;field4;field5;'
UNION ALL SELECT 2, N'x;y;6;r;3;2;w;'
UNION ALL SELECT 3, N'ttt;444;rrr;333;111;444;777;888;';

SELECT x.i, s1.Item
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.string, ';') AS s1
WHERE s1.[Index] = 4;

内联替代方案，可能稍微在规模上更友好:

An inline alternative, which might be slightly friendlier at scale:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);
GO

当然，您最终会想要修复架构.如果此列表中的第 4 个元素是重要数据，则应单独存储.

Of course ultimately you'll want to fix the schema. If the 4th element in this list is an important piece of data, you should be storing it separately.

这篇关于拆分列表时确定第n个项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！