SQL Server 2022提取JOB表URL列<artikel>标签间值的方法

针对JOB表URL列中存储的转义XML片段，以下两种方法可提取<artikel>与</artikel>之间的值：

方法一：使用SUBSTRING + CHARINDEX #

如果标签格式固定，可直接通过字符串定位提取：

SELECT
    SUBSTRING(
        URL,
        CHARINDEX('<artikel>', URL) + LEN('<artikel>'),
        CHARINDEX('</artikel>', URL) - CHARINDEX('<artikel>', URL) - LEN('<artikel>')
    ) AS ArtikelValue
FROM JOB;

逻辑说明：

• CHARINDEX('<artikel>', URL)定位起始标签的位置，加上标签长度得到内容的起始点
• 用结束标签位置减去起始标签位置再减去标签长度，得到需提取的内容长度

方法二：XML解析（更可靠） #

由于存储的是转义后的XML，可先还原为标准XML再提取节点值，避免标签位置变动的影响：

SELECT
    CAST(
        '<root>' + REPLACE(REPLACE(URL, '&lt;', '<'), '&gt;', '>') + '</root>'
    AS XML).value('(/root/ersatzteilOperations/artikel)[1]', 'NVARCHAR(100)') AS ArtikelValue
FROM JOB;

逻辑说明：

1. 替换<为<、>为>，还原XML格式
2. 添加<root>根节点（XML要求单一根元素）
3. 使用value()方法定位并提取artikel节点的文本值

注意事项 #

• 若URL列存在格式不规范的内容（如标签缺失、大小写不一致），XML方法可能报错，此时优先使用SUBSTRING方法
• 若标签存在大小写差异，可在CHARINDEX中加入COLLATE SQL_Latin1_General_CP1_CS_AS区分大小写，或用LOWER()统一转换后再定位

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