使用T-SQL，从字符串返回第n个分隔元素

Question

我需要创建一个函数，它将返回分隔字符串的第n个元素。对于数据迁移项目，我使用SQL脚本将存储在SQL Server数据库中的JSON审核记录转换为结构化报告。目标是提供脚本使用的sql脚本和sql函数，而无需任何代码。（这是一个短期修复，将在ASP.NET / MVC应用程序中添加新的审核功能时使用）可用的表格示例不缺少分隔字符串。我选择了一个Common Table Expression示例http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings示例：我想从'1,222,2,67,888,1111'返回67

Answer 1

这是缓解67（类型安全!!）的最简单的答案：

SELECT CAST('<x>' + REPLACE('1,222,2,67,888,1111',',','</x><x>') + '</x>' AS XML).value('/x[4]','int')

在下文中，您将找到如何将此变量用于字符串，分隔符和位置的变量的示例（即使对于具有XML禁止字符的边案例）

这很简单

这个问题不是关于字符串拆分方法，而是关于如何获取第n个元素。这个IMO是最容易，完全可行的方式：

这是一个真正的单行程序，可以通过空格分隔第2部分：

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';

SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

变量可以与sql:variable()或一起使用sql:column()

当然，您可以使用变量作为分隔符和位置（用于sql:column直接从查询的值中检索位置）：

DECLARE @dlmt NVARCHAR(10)=N' ';

DECLARE @pos INT = 2;

SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

带有XML禁止字符的Edge-Case

如果您的字符串可能包含禁用字符，您仍然可以这样做。FOR XML PATH首先在字符串上使用，隐式替换所有禁用字符和拟合转义序列。

如果 - 另外 - 你的分隔符是分号，这是一个非常特殊的情况。在这种情况下，我首先将分隔符替换为“＃DLMT＃”，并最终将其替换为XML标记：

SET @input=N'Some <, > and &;Other äöü@€;One more';

SET @dlmt=N';';

SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

SQL-Server 2016+的更新

很遗憾，开发人员忘记返回部分索引STRING_SPLIT。但是，使用SQL-Server 2016+，有JSON_VALUE和OPENJSON。

与JSON_VALUE我们可以将该位置作为索引”数组传递。

对于OPENJSON该文件明确规定：

当OPENJSON解析JSON数组时，该函数将JSON文本中元素的索引作为键返回。

一个字符串只1,2,3需要括号：[1,2,3]。

也就是说像一个字符串this is an example需要是["this","is","an"," example"]。

这些是非常简单的字符串操作。试试吧：

DECLARE @str VARCHAR(100)='Hello John Smith';

DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT

SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

- 参见一个位置安全的字符串分割器（从零开始）：

SELECT  JsonArray.[key] AS [Position]

       ,JsonArray.[value] AS [Part]

FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

在这篇文章中，我测试了各种方法并发现，这OPENJSON非常快。甚至比着名的“delimitedSplit8k（）”方法快得多......

Answer 2

怎么样：

CREATE FUNCTION dbo.NTH_ELEMENT (@Input NVARCHAR(MAX), @Delim CHAR = '-', @N INT = 0)

RETURNS NVARCHAR(MAX)

AS

BEGIN

RETURN (SELECT VALUE FROM STRING_SPLIT(@Input, @Delim) ORDER BY (SELECT NULL) OFFSET @N ROWS FETCH NEXT 1 ROW ONLY)

END