如何编写SQL Server函数拆分继承表的键值对数据？

这种遗留表的设计确实有点反人类，但我们可以通过自定义函数完美处理这个需求。核心思路是先解析Keys字段里的规则，提取每个键的名称、起始位置和长度，再根据这些规则从Values字段中截取对应的值，同时处理长度为-1的特殊情况。

完整函数代码 #

下面是一个可以直接使用的SQL Server函数，兼容2016及以上版本（如果是更早版本，后面会说明兼容方法）：

CREATE OR ALTER FUNCTION dbo.ParseKeyValuePairs
(
    @UserID VARCHAR(50),
    @Keys VARCHAR(MAX),
    @Values VARCHAR(MAX)
)
RETURNS @Result TABLE
(
    UserID VARCHAR(50),
    KeyName VARCHAR(100),
    KeyValue VARCHAR(MAX)
)
AS
BEGIN
    -- 第一步：拆分Keys字段为单个元素，生成带顺序的列表，过滤空值（避免末尾冒号导致的空元素）
    DECLARE @SplitKeys TABLE (Seq INT IDENTITY(1,1), Value VARCHAR(100))
    INSERT INTO @SplitKeys (Value)
    SELECT value FROM STRING_SPLIT(@Keys, ':') WHERE value <> ''

    -- 第二步：每3个元素为一组（键名、起始位置、长度），计算对应的值
    INSERT INTO @Result (UserID, KeyName, KeyValue)
    SELECT
        @UserID,
        sk1.Value AS KeyName,
        CASE 
            WHEN sk3.Value = '-1' THEN NULL -- 长度为-1时返回NULL
            -- SQL的SUBSTRING是1-based索引，所以要把0-based的起始位置加1
            ELSE SUBSTRING(@Values, CAST(sk2.Value AS INT) + 1, CAST(sk3.Value AS INT))
        END AS KeyValue
    FROM @SplitKeys sk1
    -- 关联每组的第二个元素（起始位置）
    JOIN @SplitKeys sk2 ON sk1.Seq + 1 = sk2.Seq
    -- 关联每组的第三个元素（长度）
    JOIN @SplitKeys sk3 ON sk1.Seq + 2 = sk3.Seq
    -- 只取每组的第一个元素（键名）所在的行，确保分组正确
    WHERE sk1.Seq % 3 = 1

    RETURN
END

测试示例 #

用你提供的示例数据测试这个函数：

SELECT * FROM dbo.ParseKeyValuePairs(
    '001',
    'CompanyID:0:1:FirstName:1:4:LastName:5:6:ExtranetID:11:5:Company:16:3:Application:0:-1:',
    '3ZachTaylor11057XYZ'
)

执行后会得到如下结果：

完全符合预期！

注意事项 #

1. 兼容旧版本SQL Server：如果你的SQL Server版本在2016之前（不支持STRING_SPLIT），需要替换掉拆分部分，用自定义的字符串拆分函数。比如用XML实现的拆分函数，或者CTE递归拆分，替换掉INSERT INTO @SplitKeys那部分代码。
2. 格式校验：要确保Keys字段的格式严格遵循键名:起始位置:长度:...的规则，如果元素个数不是3的倍数，函数会自动忽略不完整的组。
3. 索引转换：Keys里的起始位置是0-based的，而SQL Server的SUBSTRING函数是1-based索引，所以必须加1，这是容易出错的关键点。
4. 边界处理：如果Values字段的长度不足以支撑截取，SUBSTRING会返回空字符串，你可以根据需求调整CASE语句来处理这种情况（比如返回NULL）。

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