如何在SQL Server中动态提取\n换行符间的多段文本？

问题分析 #

你之前用SUBSTRING+CHARINDEX的写法只能固定提取某一段内容，无法处理长度和分隔数量不固定的字符串。要动态提取所有\n分隔的文本，需要用字符串拆分或递归遍历的方式实现，再结合存储过程完成API响应的入库操作。

方案1：使用STRING_SPLIT（SQL Server 2016及以上版本） #

SQL Server 2016引入的STRING_SPLIT函数可以直接拆分字符串，配合过滤条件就能得到所有有效内容：

-- 测试拆分逻辑
SELECT TRIM(value) AS split_text
FROM STRING_SPLIT('Image is Non-compliant \nFace Covered \nIncorrect Lighting \n', '\n')
WHERE TRIM(value) <> ''  -- 过滤空字符串（比如末尾的\n产生的空值）
  AND value NOT LIKE 'Image is Non-compliant%';  -- 按需排除首项

封装为存储过程 #

把拆分逻辑整合到存储过程中，接收API响应字符串并插入目标表：

CREATE PROCEDURE SaveAPIValidationErrors
    @api_response NVARCHAR(MAX)  -- API返回的字符串参数
AS
BEGIN
    SET NOCOUNT ON;

    -- 假设目标表为ValidationErrors，存储拆分后的错误文本
    INSERT INTO ValidationErrors (ErrorDescription)
    SELECT TRIM(value) AS ErrorDescription
    FROM STRING_SPLIT(@api_response, '\n')
    WHERE TRIM(value) <> ''
      AND value NOT LIKE 'Image is Non-compliant%';  -- 根据实际需求调整过滤规则
END;

方案2：递归CTE兼容旧版SQL Server #

如果你的SQL Server版本低于2016，没有STRING_SPLIT，可以用递归CTE遍历分隔符位置来拆分字符串：

-- 测试递归拆分逻辑
DECLARE @input_str NVARCHAR(MAX) = 'Image is Non-compliant \nFace Covered \nIncorrect Lighting \n';

WITH SplitCTE AS (
    -- 初始行：提取第一个\n前的内容
    SELECT
        1 AS seq_num,
        CHARINDEX('\n', @input_str) AS next_pos,
        SUBSTRING(@input_str, 1, CHARINDEX('\n', @input_str) - 1) AS split_text
    UNION ALL
    -- 递归遍历后续的\n
    SELECT
        seq_num + 1,
        CHARINDEX('\n', @input_str, next_pos + 1),
        SUBSTRING(@input_str, next_pos + 1, CHARINDEX('\n', @input_str, next_pos + 1) - next_pos - 1)
    FROM SplitCTE
    WHERE next_pos > 0  -- 直到找不到下一个\n
)
SELECT TRIM(split_text) AS split_text
FROM SplitCTE
WHERE TRIM(split_text) <> ''
  AND split_text NOT LIKE 'Image is Non-compliant%';

封装为存储过程 #

同样把递归逻辑整合到存储过程：

CREATE PROCEDURE SaveAPIValidationErrors_Legacy
    @api_response NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    WITH SplitCTE AS (
        SELECT
            1 AS seq_num,
            CHARINDEX('\n', @api_response) AS next_pos,
            SUBSTRING(@api_response, 1, CHARINDEX('\n', @api_response) - 1) AS split_text
        UNION ALL
        SELECT
            seq_num + 1,
            CHARINDEX('\n', @api_response, next_pos + 1),
            SUBSTRING(@api_response, next_pos + 1, CHARINDEX('\n', @api_response, next_pos + 1) - next_pos - 1)
        FROM SplitCTE
        WHERE next_pos > 0
    )
    INSERT INTO ValidationErrors (ErrorDescription)
    SELECT TRIM(split_text) AS ErrorDescription
    FROM SplitCTE
    WHERE TRIM(split_text) <> ''
      AND split_text NOT LIKE 'Image is Non-compliant%';
END;

使用说明 #

1. 替换ValidationErrors和ErrorDescription为你实际的表名和列名
2. 过滤条件value NOT LIKE 'Image is Non-compliant%'可以根据需求调整，比如不需要排除首项就删除这一行
3. 如果API返回的是真实换行符（不是转义的\n），把拆分符从'\n'换成CHAR(10)

内容的提问来源于stack exchange，提问作者Sindhu C L