SQL Server 2016 中 STRING_SPLIT 位置的返回值 [英] return value at a position from STRING_SPLIT in SQL Server 2016

问题描述

我可以在 SQL Server 2016 或更高版本中使用 STRING_SPLIT 函数返回特定位置的值吗?

Can I return a value at a particular position with the STRING_SPLIT function in SQL Server 2016 or higher?

我知道不能保证选择的顺序，但它是否与 STRING_SPLIT 一起使用?

I know the order from a select is not guaranteed, but is it with STRING_SPLIT?

DROP TABLE IF EXISTS #split

SELECT 'z_y_x' AS splitIt
INTO #split UNION
SELECT 'a_b_c'

SELECT * FROM #split;

WITH cte
AS (
SELECT      ROW_NUMBER() OVER ( PARTITION BY s.splitIt ORDER BY s.splitIt ) AS position,
            s.splitIt,
            value
FROM        #split s
CROSS APPLY STRING_SPLIT(s.splitIt, '_')
)
SELECT * FROM cte WHERE position = 2

这是否总是返回第二个元素的值?b 代表 a_b_c，y 代表 z_y_x?

Will this always return the value at the 2nd element? b for a_b_c and y for z_y_x?

我不明白为什么 Microsoft 不在此函数的值旁边返回位置指示器列.

I don't understand why Microsoft doesn't return a position indicator column alongside the value for this function.

推荐答案

简单的答案是，不.到目前为止，微软拒绝在 STRING_SPLIT 中提供 Ordinal 位置作为返回数据集的一部分.恐怕您需要使用不同的解决方案.例如 Jeff Moden 的 DelimitedSplit8k.

The simple answer is, no. Microsoft so far have refused to provide Ordinal position as part of the return dataset in STRING_SPLIT. You'll need to use a different solution I'm afraid. For example Jeff Moden's DelimitedSplit8k.

(是的，我意识到这或多或少只是一个链接的答案，但是，将杰夫的解决方案粘贴在这里实际上是抄袭).

如果您要使用 Jeff 的解决方案，那么您将能够执行以下操作:

If you were to use Jeff's solution, then you would be able to do something like:

SELECT *
FROM dbo.DelimitedSplit8K('a,b,c,d,e,f,g,h,i,j,k',',') DS
WHERE ItemNumber = 2;

当然，您可能会传递列而不是文字字符串.

Of course, you'd likely be passing column rather than a literal string.

这篇关于SQL Server 2016 中 STRING_SPLIT 位置的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！