SQL Server 2012 如何按标签拆分单列字符串生成指定三列

你原有代码的核心问题是SUBSTRING只定义了起始位置、未截取对应字段的结束位置，导致取出的内容会包含后续字段的冗余信息，同时也没有去掉字段前的HTML标签前缀。

以下是可直接运行的实现代码：

WITH Positions AS (
    SELECT
        ColumnName,
        -- 定位各字段的起始标记位置
        CHARINDEX('<b>Application</b> : ', ColumnName) + LEN('<b>Application</b> : ') AS AppStart,
        CHARINDEX('<br/><b>Access Level</b> : ', ColumnName) AS AppEnd,
        CHARINDEX('<br/><b>Access Level</b> : ', ColumnName) + LEN('<br/><b>Access Level</b> : ') AS AccessStart,
        CHARINDEX('<br/><b>Restrictions</b> : ', ColumnName) AS AccessEnd,
        CHARINDEX('<br/><b>Restrictions</b> : ', ColumnName) + LEN('<br/><b>Restrictions</b> : ') AS RestrictStart
    FROM TableName
)
SELECT
    -- 截取Application：从AppStart到AppEnd之前的内容
    SUBSTRING(ColumnName, AppStart, AppEnd - AppStart) AS [Application],
    -- 截取Access Level：从AccessStart到AccessEnd之前的内容
    SUBSTRING(ColumnName, AccessStart, AccessEnd - AccessStart) AS [Access Level],
    -- 截取Restrictions：从RestrictStart到字符串末尾
    SUBSTRING(ColumnName, RestrictStart, LEN(ColumnName) - RestrictStart + 1) AS [Restrictions]
FROM Positions

如果需要兼容部分行缺失某个标签的场景，可以用ISNULL处理位置参数，避免CHARINDEX返回0导致截取报错，示例如下：

WITH Positions AS (
    SELECT
        ColumnName,
        ISNULL(CHARINDEX('<b>Application</b> : ', ColumnName) + LEN('<b>Application</b> : '), 1) AS AppStart,
        ISNULL(NULLIF(CHARINDEX('<br/><b>Access Level</b> : ', ColumnName), 0), LEN(ColumnName)+1) AS AppEnd,
        ISNULL(CHARINDEX('<br/><b>Access Level</b> : ', ColumnName) + LEN('<br/><b>Access Level</b> : '), 1) AS AccessStart,
        ISNULL(NULLIF(CHARINDEX('<br/><b>Restrictions</b> : ', ColumnName), 0), LEN(ColumnName)+1) AS AccessEnd,
        ISNULL(CHARINDEX('<br/><b>Restrictions</b> : ', ColumnName) + LEN('<br/><b>Restrictions</b> : '), 1) AS RestrictStart
    FROM TableName
)
SELECT
    NULLIF(SUBSTRING(ColumnName, AppStart, CASE WHEN AppEnd > AppStart THEN AppEnd - AppStart ELSE 0 END), '') AS [Application],
    NULLIF(SUBSTRING(ColumnName, AccessStart, CASE WHEN AccessEnd > AccessStart THEN AccessEnd - AccessStart ELSE 0 END), '') AS [Access Level],
    NULLIF(SUBSTRING(ColumnName, RestrictStart, CASE WHEN LEN(ColumnName) >= RestrictStart THEN LEN(ColumnName) - RestrictStart + 1 ELSE 0 END), '') AS [Restrictions]
FROM Positions

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