SQL Server 2016中替换JSON列内特定逗号为||的方法咨询

在SQL Server 2016中，利用内置的JSON函数就能完成这个需求，不需要依赖CLR。核心思路是先解析JSON提取目标字段，替换逗号后再重新生成JSON：

步骤说明 #

• 使用OPENJSON解析原始JSON，将结构化数据拆分成关系型行/列
• 对problemTypeID和value字段执行字符串替换，把逗号,替换为双竖线||
• 用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER将处理后的字段重新组合成与原始结构一致的JSON

完整T-SQL代码 #

假设你的表名为YourTable，存储JSON的列名为JsonColumn，执行以下语句可得到处理后的结果：

SELECT 
    REPLACE(j.problemTypeID, ',', '||') AS problemTypeID,
    j.field,
    j.fromSidePanel,
    j.operator,
    REPLACE(j.value, ',', '||') AS value
FROM YourTable t
CROSS APPLY OPENJSON(t.JsonColumn) WITH (
    problemTypeID NVARCHAR(MAX),
    field NVARCHAR(50),
    fromSidePanel BIT,
    [operator] NVARCHAR(10),
    value NVARCHAR(MAX)
) j
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

代码解释 #

1. OPENJSON(...) WITH (...)：将JSON列解析为关系型数据，明确指定每个字段的数据类型
2. REPLACE(...)：针对包含数字列表的problemTypeID和value字段，完成逗号到双竖线的替换
3. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER：将处理后的关系数据重新序列化为JSON字符串，WITHOUT_ARRAY_WRAPPER保证生成的JSON与原始结构一致（单个对象的数组格式）

如果需要直接更新表中的JSON列，可使用以下更新语句：

UPDATE YourTable
SET JsonColumn = (
    SELECT 
        REPLACE(j.problemTypeID, ',', '||') AS problemTypeID,
        j.field,
        j.fromSidePanel,
        j.operator,
        REPLACE(j.value, ',', '||') AS value
    FROM OPENJSON(YourTable.JsonColumn) WITH (
        problemTypeID NVARCHAR(MAX),
        field NVARCHAR(50),
        fromSidePanel BIT,
        [operator] NVARCHAR(10),
        value NVARCHAR(MAX)
    ) j
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

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