如何在SQL函数CHARINDEX中使用RegEx查找第n次出现

Question

下面的算法的目的是从密钥中提取特定的字段集(在本例中，提取前两个字段)，它可以工作。字段由冒号分隔：

declare @key nvarchar (max);
declare @pos int;
declare @fields nvarchar (max);

set @key = 'Field-1:Field-2:Field-3:Field-4:Field-5';
set @pos = charindex(':', @key, charindex (':', @key) + 1);
set @fields = left(@key, @pos - 1);

select @fields;

结果:字段-1:字段-2

Microsoft文档说，第一个参数是表达式，但我认为它们在CHARINDEX上下文中的意思是，这个表达式应该计算为字符串文本；因此，下面传递RegEx以获得第二次出现的尝试不起作用；很明显，要么它不受支持，要么我使用了错误的语法：

--match the second occurrence of the delimiter using RegEx
set @pos = charindex (':.*?(:)', @key);

换句话说，是否有可能使用RegEx在给定文本中找到分隔符第n次出现的位置，这样我就可以避免几个嵌套的CHARINDEX或要解析的循环？撇开不谈，如果n被作为参数传递，那么我甚至不能再使用静态嵌套了.

提前谢谢你的帮助。

环境: Microsoft 2014 (SP3)标准版(64位)

Answer 1

没有简单的方法，只有技巧，来提取字符串的第n个子字符串。下面是一种基于集合的递归CTE方法：

DECLARE @str NVARCHAR(MAX) = N'Field-1:Field-2:Field-3:Field-4:Field-5';
DECLARE @num INT = 4;

WITH rcte AS (
    SELECT str = @str
         , n = 1
         , p = CHARINDEX(':', @str, 1)
    UNION ALL
    SELECT str
         , n + 1
         , CHARINDEX(':', str, p + 1)
    FROM rcte
    WHERE n < @num AND p > 0
)
SELECT CASE WHEN p > 0 THEN SUBSTRING(str, 1, p - 1) ELSE str END
FROM rcte
WHERE n = @num;

如果循环是一个选项，那么：

DECLARE @str NVARCHAR(MAX) = N'Field-1:Field-2:Field-3:Field-4:Field-5';
DECLARE @num INT = 4;
DECLARE @n INT = 0;
DECLARE @p INT = 0;

WHILE 1 = 1
BEGIN
    SET @n = @n + 1;
    SET @p = CHARINDEX(':', @str, @p + 1);
    IF @n = @num OR @p = 0 BREAK;
END;

SELECT CASE WHEN @p > 0 THEN SUBSTRING(@str, 1, @p - 1) ELSE @str END;

DB<>Fiddle

Answer 2

请注意，我将":“添加到字符串的末尾。如有必要，请作出调整。

declare @n int = 5
declare @i int = 0
declare @len int = 0
declare @pos int = 0
declare @c char(1) = ":"
declare @str varchar(256)

set @str = "Field-1:Field-2:Field-3:Field-4:Field-5:"
set @len = len(@str)

while(@i < @n and @pos < @len)
begin
    set @pos = charindex(@c, @str, @pos + 1)
    set @i = @i + 1
end

select substring(@str, 1, @pos -1)