SQL Server中如何从自由文本varchar列提取多值为单独记录

现有一张表，其varchar列包含自由文本及实验室数值，文本无结构化，但实验室值可通过方括号（[]）识别（例如[NA/bl: 137]）。需要为每个LabId提取所有实验室值生成单独记录，每条原记录的数值数量不固定（0到多个）。

测试数据集 #

CREATE TABLE #TestTable123(
    [_record_number] int IDENTITY(1,1) PRIMARY KEY,
    [LabId] Integer,
    [LabDate] datetime,
    LabDescription varchar(200)
);

insert into #TestTable123(
    LabId,
    LabDate,
    LabDescription
) values
('1001', '2022-02-13', 'questionnaire completed, labvalues [NA/bl: 141] [HCT/blo: 0.39] [HGB: 8.2] [WBC: 7.0], cardiotest completed'),
('1002', '2021-04-10', 'noshow'),
('1003', '2021-10-18', 'questionnaire completed, lab [NA/bl: 138] [HCT/blo: 0.29] [HGB: 4.7]'),
('1004', '2022-06-07', 'labresults [NA/bl: 140] [HCT/blo: 0.31] [HGB: 5.5] [WBC: 3.2], questionnaire completed'),
('1005', '2021-11-26', 'lab [NA/bl: 136] [HCT/blo: 0.38] [HGB: 6.8]')

现有问题 #

尝试的SQL仅能提取每条文本中的首个实验室值：

select
    LabId,
    substring(LabDescription, charindex('[', LabDescription), charindex(']', LabDescription)-charindex('[', LabDescription) + 1)
from
    #TestTable123
where
    LabDescription like '%\[%' escape '\'

期望结果 #

LabId  LabResult_extracted
1001   [NA/bl: 141]
1001   [HCT/blo: 0.39]
1001   [HGB: 8.2]
1001   [WBC: 7.0]
1003   [NA/bl: 138]
1003   [HCT/blo: 0.29]
1003   [HGB: 4.7]
1004   [NA/bl: 140]
1004   [HCT/blo: 0.31]
1004   [HGB: 5.5]
1004   [WBC: 3.2]
1005   [NA/bl: 136]
1005   [HCT/blo: 0.38]
1005   [HGB: 6.8]

最优解决方案 #

方法1：递归CTE（通用所有SQL Server版本） #

通过递归CTE逐次提取每个方括号包裹的实验室值，直到当前文本中不再包含方括号：

WITH RecursiveExtract AS (
    -- 初始查询：提取第一条匹配的实验室值，并保留剩余文本
    SELECT
        LabId,
        SUBSTRING(LabDescription, CHARINDEX('[', LabDescription), CHARINDEX(']', LabDescription) - CHARINDEX('[', LabDescription) + 1) AS LabResult_extracted,
        STUFF(LabDescription, 1, CHARINDEX(']', LabDescription), '') AS RemainingText
    FROM #TestTable123
    WHERE LabDescription LIKE '%\[%' ESCAPE '\'

    UNION ALL

    -- 递归查询：继续从剩余文本中提取下一个[]内容
    SELECT
        LabId,
        SUBSTRING(RemainingText, CHARINDEX('[', RemainingText), CHARINDEX(']', RemainingText) - CHARINDEX('[', RemainingText) + 1) AS LabResult_extracted,
        STUFF(RemainingText, 1, CHARINDEX(']', RemainingText), '') AS RemainingText
    FROM RecursiveExtract
    WHERE RemainingText LIKE '%\[%' ESCAPE '\'
)
SELECT LabId, LabResult_extracted
FROM RecursiveExtract
ORDER BY LabId, LabResult_extracted;

方法2：数字表法（适用于SQL Server 2012+） #

临时生成连续数字表，通过数字定位每个方括号的位置来提取值：

-- 临时生成1-100的连续数字，覆盖最大可能的数值数量
WITH Numbers AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM sys.all_columns
)
SELECT
    t.LabId,
    SUBSTRING(t.LabDescription, CHARINDEX('[', t.LabDescription, n.N), CHARINDEX(']', t.LabDescription, CHARINDEX('[', t.LabDescription, n.N)) - CHARINDEX('[', t.LabDescription, n.N) + 1) AS LabResult_extracted
FROM #TestTable123 t
JOIN Numbers n ON CHARINDEX('[', t.LabDescription, n.N) > 0
WHERE t.LabDescription LIKE '%\[%' ESCAPE '\'
ORDER BY t.LabId, LabResult_extracted;

方法3：SQL Server 2022+ 简化写法 #

利用STRING_SPLIT的ordinal参数拆分文本后过滤处理：

SELECT
    t.LabId,
    '[' + LEFT(s.value, CHARINDEX(']', s.value)) AS LabResult_extracted
FROM #TestTable123 t
CROSS APPLY STRING_SPLIT(t.LabDescription, '[', 1) s
WHERE s.value LIKE '%]%'
AND t.LabDescription LIKE '%\[%' ESCAPE '\'
ORDER BY t.LabId, s.ordinal;

说明 #

• 递归CTE方法兼容性最好，无需依赖特定版本或额外资源，适合绝大多数场景。
• 数字表法在处理大批量数据时性能更优，需确保数字范围足够覆盖所有可能的数值数量。
• SQL Server 2022+的方法代码最简洁，充分利用了新版本的字符串拆分功能。

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