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

Question

我需要创建一个函数，它将返回分隔字符串的第 n 个元素。

对于数据迁移项目，我使用 SQL 脚本将存储在 SQL Server 数据库中的 JSON 审核记录转换为结构化报告。目标是在没有任何代码的情况下交付一个 sql 脚本和该脚本使用的 sql 函数。

(这是一个短期修复，将在 ASP.NET/MVC 应用程序中添加新的审核功能时使用)

不乏可用的分隔字符串到表示例。我选择了一个通用表表达式示例 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个元素。在我看来，最简单、完全内联的方法是:

这是一个真正的单行，用于让第 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 禁止字符的边缘情况

如果您的字符串可能包含禁止字符，您仍然可以这样做。只需首先在字符串上使用 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 documentation明确指出:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

像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

在 this post我测试了各种方法，发现 OPENJSON 非常快。甚至比著名的“delimitedSplit8k()”方法快得多...

##UPDATE 2 - 获取类型安全的值

我们可以通过使用双倍的[[]]来使用数组中的数组。这允许使用类型化的 WITH 子句:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
    ,TheSecondFragment INT '$[1]'
    ,TheThirdFragment DATE '$[2]') ValuesFromTheArray