如何用SQL为SFMC中DE表JSON内EMPLOYEE_DEPT_NO值加单引号

问题背景 #

在Salesforce Marketing Cloud（SFMC）的Incoming_data数据扩展中，Data_Json字段存储的JSON数据里，部分记录的EMPLOYEE_DEPT_NO字段值为数字格式（如'EMPLOYEE_DEPT_NO':1），需要将其修改为带单引号的字符串格式（如'EMPLOYEE_DEPT_NO':'1'）。

数据扩展结构：

Id -- Text(40)
Data_Json -- Text(4000)

实现SQL语句 #

利用SFMC支持的正则替换函数，结合模式匹配过滤目标记录，执行以下更新操作：

UPDATE Incoming_data
SET Data_Json = REGEXP_REPLACE(
    Data_Json,
    '''EMPLOYEE_DEPT_NO'':(\d+)',
    '''EMPLOYEE_DEPT_NO'':''$1'''
)
WHERE PATINDEX('%''EMPLOYEE_DEPT_NO'':%', Data_Json) > 0;

说明 #

1. 正则匹配逻辑：'''EMPLOYEE_DEPT_NO'':(\d+)'匹配JSON中'EMPLOYEE_DEPT_NO':后跟随1个或多个数字的片段，(\d+)捕获数字部分作为分组。
2. 替换规则：'''EMPLOYEE_DEPT_NO'':''$1'''将匹配到的片段替换为带单引号的格式，$1引用之前捕获的数字分组。
3. 过滤条件：WHERE子句确保仅修改包含EMPLOYEE_DEPT_NO字段的记录，避免无意义的更新操作。
4. SFMC执行注意事项：该SQL需在SFMC的SQL Query Activity中执行，确保Incoming_data数据扩展具备可写权限，且Data_Json字段长度（4000）足够容纳修改后的内容。

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