SQL Server：将AD计算机描述字段拆分至多列的技术需求

我们已通过PowerShell脚本将AD计算机账户导入SQL Server，现在需要把存储在[Description]字段中的复合信息拆分为独立列。目前已完成主描述内容和资产标签的拆分，还需提取型号、产品编号、序列号这三个字段。

原数据示例 #

[Description]
Joe Smith Laptop - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF3662A2F - AT#B-10132
John Smith Laptop - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF36620TM
Susan Smith WFH - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF36620QA

目标拆分结果 #

[Description]       [Model]                          [ProductNumber] [SerialNumber] [Asset Tag]
Joe Smith Laptop    HP EliteBook 840 G8 Notebook PC  359Z6UT#ABA     6EF3662A2F     B-10132
John Smith Laptop   HP EliteBook 840 G8 Notebook PC  359Z6UT#ABA     6EF36620TM
Susan Smith WFH     HP EliteBook 840 G8 Notebook PC  359Z6UT#ABA     6EF36620QA

完整SQL实现代码 #

以下是包含所有字段拆分的SQL代码，可直接用于创建视图：

SELECT
    -- 主描述内容（原已实现）
    LEFT([Description], CHARINDEX(' - HP ', [Description])) AS [Description],
    -- 提取型号：从" - HP "后到下一个" - "之间的内容
    SUBSTRING(
        [Description],
        CHARINDEX(' - HP ', [Description]) + 5,
        CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) - (CHARINDEX(' - HP ', [Description]) + 5)
    ) AS [Model],
    -- 提取产品编号：型号后的第一个" - "到下一个" - "之间的内容
    SUBSTRING(
        [Description],
        CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3,
        CHARINDEX(' - ', [Description], CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3) - (CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3)
    ) AS [ProductNumber],
    -- 提取序列号：产品编号后的" - "到资产标签（如果存在）或末尾的内容
    CASE
        WHEN CHARINDEX(' - AT#', [Description]) > 0 THEN
            SUBSTRING(
                [Description],
                CHARINDEX(' - ', [Description], CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3) + 3,
                CHARINDEX(' - AT#', [Description]) - (CHARINDEX(' - ', [Description], CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3) + 3)
            )
        ELSE
            SUBSTRING(
                [Description],
                CHARINDEX(' - ', [Description], CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3) + 3,
                LEN([Description]) - (CHARINDEX(' - ', [Description], CHARINDEX(' - ', [Description], CHARINDEX(' - HP ', [Description]) + 5) + 3) + 3) + 1
            )
    END AS [SerialNumber],
    -- 资产标签（兼容无资产标签的情况）
    CASE
        WHEN CHARINDEX(' - AT#', [Description]) > 0 THEN
            RIGHT([Description], LEN([Description]) - CHARINDEX(' - AT#', [Description]) - 5)
        ELSE
            NULL
    END AS [Asset Tag]
FROM
    YourTableName; -- 替换为实际表名

代码说明 #

1. 主描述：通过LEFT和CHARINDEX截取到" - HP "之前的内容。
2. 型号：定位" - HP "的起始位置后偏移5位，截取到下一个" - "的位置。
3. 产品编号：在型号结束位置的基础上，找到下一个" - "并偏移3位，再截取到下一个" - "的位置。
4. 序列号：分两种情况处理：存在" - AT#"时截取到该标识前，不存在则截取到字符串末尾。
5. 资产标签：保留原实现逻辑，新增CASE语句处理无资产标签的场景，返回NULL。

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