SQL Server 2012中读取JSON列可变长度的remittanceAdviceId值

由于SQL Server 2012没有原生JSON解析函数，我们可以通过字符串定位函数的组合来提取remittanceAdviceId的可变长度值，具体实现如下：

完整SQL语句 #

SELECT 
    remittanceAdviceId = CASE
        WHEN E.ResponseJSON IS NOT NULL 
             AND CHARINDEX('"remittanceAdviceId"', E.ResponseJSON) > 0 THEN
            SUBSTRING(
                E.ResponseJSON,
                -- 定位值的起始位置：跳过"remittanceAdviceId":"的字符
                CHARINDEX('":"', E.ResponseJSON, CHARINDEX('"remittanceAdviceId"', E.ResponseJSON)) + 2,
                -- 计算值的长度：从起始位置到下一个引号的距离
                CHARINDEX('"', E.ResponseJSON, 
                    CHARINDEX('":"', E.ResponseJSON, CHARINDEX('"remittanceAdviceId"', E.ResponseJSON)) + 2
                ) - (CHARINDEX('":"', E.ResponseJSON, CHARINDEX('"remittanceAdviceId"', E.ResponseJSON)) + 2)
            )
        ELSE
            NULL
    END
FROM 
    YourTableName E; -- 替换为你的实际表名

逻辑说明 #

1. 定位目标键：用CHARINDEX('"remittanceAdviceId"', E.ResponseJSON)找到键的起始位置
2. 定位值的起点：从键的位置开始，找到":"的位置，加2后就是值的第一个字符位置
3. 定位值的终点：从值的起点开始，找到下一个双引号的位置，两者的差值就是值的长度
4. 截取内容：用SUBSTRING提取两个位置之间的内容，自动适配值的长度

注意事项 #

• 确保JSON格式规范：键和值都使用双引号，且值内无未转义的双引号（若有转义需额外处理）
• 若存在大小写不一致的情况，可嵌套LOWER()函数统一转换后再查找

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