分裂列表时，确定第n个项目？(Identify nth item when splitting a

我想提供一个ROW_NUMBER中的CTE能够选择一个特定的项目出的序列与一个给定的索引（如一个数组索引arr[index] ）。 当然，“序列”是来自一个表值函数（表Split ）。

那么，如何创建索引时，它的不可用？ 这是查询：

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)订单按字母顺序排列，但实际上我想用（不可用）指数订购。

请注意，这个问题arised在我的答案上这个所谓的问题 。

Answer 1:

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

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.

Answer 2:

如果您在串中的值是唯一可以使用ORDER BY charindex(';'+item+';', ';'+@string+';')中OVER()子句。

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 charindex(';'+item+';', ';'+@string+';')) 
         FROM   dbo.Split(@string, ';')) 
SELECT TOP 1 item 
FROM   cte 
WHERE  rn = @index 

charindex返回位置@string对于每个提取的item ，从而rn根据在位置上产生@string 。

SQL小提琴

文章来源: Identify nth item when splitting a list?