如何解决STRING_SPLIT拆分多余逗号干扰及自定义分隔符问题？

关于多字符分隔符的问题 #

STRING_SPLIT 仅支持单个字符作为分隔符，无法直接传入“1. ”“2. ”这类多字符格式的分隔符。如果需要基于这类模式拆分，必须通过其他字符串处理方法实现。

解决项内含逗号的拆分问题 #

你的字符串结构是每个有效项以“数字. ”开头，所以应该基于这个固定模式拆分，而不是用逗号。以下提供两种可行方案：

方案1：递归CTE（兼容SQL Server 2012及以上） #

DECLARE @input NVARCHAR(MAX) = '1. Archiviazione, 2. Conservazione in archivi, ad accesso selezionato, 3. Conservazione in contenitori muniti di serratura, 4. Controllo degli accessi fisici, 5. Controllo degli accessi logici, 6. Custodia atti e documenti, 7. Formazione degli incaricati, 8. Sicurezza dei siti web';

WITH SplitCTE AS (
    -- 初始化提取第一个项
    SELECT 
        1 AS ItemNumber,
        SUBSTRING(@input, 1, PATINDEX('% [0-9]. %', @input + ' 9. ') - 1) AS Item,
        PATINDEX('% [0-9]. %', @input + ' 9. ') + 1 AS NextStartPos
    WHERE @input IS NOT NULL AND @input <> ''
    
    UNION ALL
    
    -- 递归提取后续项
    SELECT 
        ItemNumber + 1,
        SUBSTRING(@input, NextStartPos, PATINDEX('% [0-9]. %', SUBSTRING(@input, NextStartPos, LEN(@input)) + ' 9. ') - 1),
        NextStartPos + PATINDEX('% [0-9]. %', SUBSTRING(@input, NextStartPos, LEN(@input)) + ' 9. ')
    FROM SplitCTE
    WHERE NextStartPos <= LEN(@input)
)
SELECT ItemNumber, TRIM(Item) AS Item
FROM SplitCTE;

方案2：OPENJSON结合正则替换（SQL Server 2017及以上） #

利用REGEXP_REPLACE识别“数字. ”模式，将字符串转换为JSON数组后拆分：

DECLARE @input NVARCHAR(MAX) = '1. Archiviazione, 2. Conservazione in archivi, ad accesso selezionato, 3. Conservazione in contenitori muniti di serratura, 4. Controllo degli accessi fisici, 5. Controllo degli accessi logici, 6. Custodia atti e documenti, 7. Formazione degli incaricati, 8. Sicurezza dei siti web';

-- 转换为JSON数组格式
DECLARE @json NVARCHAR(MAX) = '["' + REGEXP_REPLACE(@input, '\s*([0-9]+)\. ', '","') + '"]';
-- 修正开头多余的逗号
SET @json = STUFF(@json, 1, 2, '');

SELECT 
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ItemNumber,
    TRIM(value) AS Item
FROM OPENJSON(@json);

这两种方案都会按“数字. ”划分的完整项拆分，不会因为项内部的逗号导致拆分错误。

内容的提问来源于stack exchange，提问作者PedroPandolfo