我在表中有一列以格式存储:
{"field1":"val1","field2":"val4"}
{"field1":"val2","field2":"val5"}
{"field1":"val3","field2":"val6"}
我需要删除所有的field1值(例如“field1”:“val1”,“field1”:“val2”,“field1”:“val3”),结果应该是
{"field2":"val4"}
{"field2":"val5"}
{"field2":"val6"}
我试图通过替换实现这一点,但卡在’“field1”中:“val1”’字符串val1可以是任何值,如null,某个整数.
UPDATE emp SET col = REPLACE(col, '"field1":"val1"', '')
由于val1的动态值,我陷入困境.
解决方法:
你可以这样做:
SELECT SUBSTRING(Field, 1, INSTR(Field, '"field1"')) + SUBSTRING(Field, INSTR(Field, '"field2"'), LENGTH(Field)) FROM @Temp
我不知道这是否有效,但这是个主意. (无法测试ATM)
这是MsSQL等价物(工作,刚测试!):
SELECT SUBSTRING(Field, 0, CHARINDEX('"field1"', Field)) + SUBSTRING(Field, CHARINDEX('"field2"', Field), LEN(Field)) FROM @Temp
标签:mysql,sql,mysql-json
来源: https://codeday.me/bug/20190715/1465791.html