SQL Server文本列提取子标题下数据并生成对应列

针对你遇到的可变长度内容提取问题，这里提供两种实用方案：

方案一：固定子标题的字符串精准提取 #

如果子标题（NAME、COMPANY、ADDRESS）是固定的，直接通过字符串定位函数提取即可，同时处理text类型的兼容性和无结尾分隔符的情况：

SELECT 
    ID,
    -- 提取NAME对应值
    LTRIM(RTRIM(SUBSTRING(
        CONVERT(varchar(max), [Text]),
        CHARINDEX('NAME:', [Text]) + 5, -- 跳过"NAME:"的5个字符
        COALESCE(
            NULLIF(CHARINDEX('.', CONVERT(varchar(max), [Text]), CHARINDEX('NAME:', [Text])), 0) - (CHARINDEX('NAME:', [Text]) + 5),
            LEN(CONVERT(varchar(max), [Text])) - (CHARINDEX('NAME:', [Text]) + 5) + 1
        )
    ))) AS Name,
    -- 提取COMPANY对应值
    LTRIM(RTRIM(SUBSTRING(
        CONVERT(varchar(max), [Text]),
        CHARINDEX('COMPANY:', [Text]) + 8, -- 跳过"COMPANY:"的8个字符
        COALESCE(
            NULLIF(CHARINDEX('.', CONVERT(varchar(max), [Text]), CHARINDEX('COMPANY:', [Text])), 0) - (CHARINDEX('COMPANY:', [Text]) + 8),
            LEN(CONVERT(varchar(max), [Text])) - (CHARINDEX('COMPANY:', [Text]) + 8) + 1
        )
    ))) AS Company,
    -- 提取ADDRESS对应值
    LTRIM(RTRIM(SUBSTRING(
        CONVERT(varchar(max), [Text]),
        CHARINDEX('ADDRESS:', [Text]) + 8, -- 跳过"ADDRESS:"的8个字符
        COALESCE(
            NULLIF(CHARINDEX('.', CONVERT(varchar(max), [Text]), CHARINDEX('ADDRESS:', [Text])), 0) - (CHARINDEX('ADDRESS:', [Text]) + 8),
            LEN(CONVERT(varchar(max), [Text])) - (CHARINDEX('ADDRESS:', [Text]) + 8) + 1
        )
    ))) AS Address
FROM YourTableName;

关键说明： #

• CONVERT(varchar(max), [Text])：text是SQL Server已弃用的类型，转成varchar(max)才能兼容大部分字符串函数。
• COALESCE + NULLIF：处理最后一个字段无.结尾的情况，自动取到字符串末尾。
• LTRIM/RTRIM：清理值前后的多余空格。

方案二：拆分键值对后透视（适配子标题变化场景） #

如果后续子标题可能新增或变动，先拆分所有键值对再转列更灵活（需SQL Server 2016及以上版本）：

WITH SplitData AS (
    SELECT 
        ID,
        LTRIM(RTRIM(value)) AS KeyValue
    FROM YourTableName
    CROSS APPLY STRING_SPLIT(CONVERT(varchar(max), [Text]), '. ')
    WHERE value <> '' -- 过滤分割后产生的空项
),
KeyValuePairs AS (
    SELECT 
        ID,
        -- 拆分键名（冒号前部分）
        LTRIM(RTRIM(SUBSTRING(KeyValue, 1, CHARINDEX(':', KeyValue) - 1))) AS KeyName,
        -- 拆分对应值（冒号后部分）
        LTRIM(RTRIM(SUBSTRING(KeyValue, CHARINDEX(':', KeyValue) + 1, LEN(KeyValue)))) AS KeyValue
    FROM SplitData
)
SELECT ID, [NAME] AS Name, [COMPANY] AS Company, [ADDRESS] AS Address
FROM KeyValuePairs
PIVOT (
    MAX(KeyValue) FOR KeyName IN ([NAME], [COMPANY], [ADDRESS])
) AS PivotTable;

关键说明： #

• STRING_SPLIT：按. 分割每个键值对，得到单个键: 值项。
• PIVOT：将拆分后的键名转成列，自动匹配对应的值。
• 若版本低于2016，可替换为自定义字符串拆分函数（如基于XML的拆分逻辑）。

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