MySQL 從字元串中間刪除引號，同時保持字元串的其餘部分完好無損

我的表中有一些欄位包含類似於以下的數據：

{"item1":"stuff","item2":"12345","item3":0,"item4":"this field contains some data that has "quotes" in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"}

我需要更新數據以刪除該item4部分的引號，同時保持其餘數據不變。

例如這個：

"item4":"this field contains some data that has "quotes" in it that need to be removed"

需要變成：

"item4":"this field contains some data that has quotes in it that need to be removed"

我可以通過以下方式毫無問題地刪除引號：

SET @dataval =('{"item1":"stuff","item2":"12345","item3":0,"item4":"this field contains some data that has "quotes" in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"}');

select REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(@dataval,'"item4":"',-1),'","item5"',1),'"','');

但是，當然，這會失去數據的重置。

有沒有辦法在一個查詢中執行此操作，還是我需要執行一些操作，例如將數據複製到臨時表中，刪除引號，然後REPLACE使用修改後的數據對原始數據執行操作？

一種選擇是更新列。所有提到的步驟都可以在這個小提琴上找到，在使用建議的生產步驟之前，請始終使用測試環境。

假設我們有下表：

create table test(
dataval varchar(255) );

insert into test values
('"item1":"stuff","item2":"12345","item3":0,"item4":"this field contains some data that has "quotes" in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"'),
('"item1":"stuff","item2":"12345","item3":0,"item4":"this uotes" in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"');

下面的建議考慮到"item4":"（這將是開始）和","item5"（這將是我們將更新的字元串部分的結尾）將存在。

要選擇和之間的字元串"item4":"，","item5"我們使用：

select replace(substring_index(substring_index(dataval, '"item4":"', -1), '","item5":', 1),'"','') as new_dataval
from test;

結果：

new_dataval
this field contains some data that has "quotes" in it that need to be removed
this uotes" in it that need to be removed

要從字元串中刪除雙引號，請使用：

select replace(substring_index(substring_index(dataval, '"item4":"', -1), '","item5":', 1),'"','') as new_dataval
from test;

結果：

new_dataval
this field contains some data that has quotes in it that need to be removed
this uotes in it that need to be removed

我們可以使用上面的查詢來更新表：

UPDATE test
SET
dataval = REPLACE(dataval, substring_index(substring_index(dataval, '"item4":"', -1), '","item5":', 1), replace(substring_index(substring_index(dataval, '"item4":"', -1), '","item5":', 1),'"',''))

現在，表格將是：

select dataval
from test;

dataval
"item1":"stuff","item2":"12345","item3":0,"item4":"this field contains some data that has quotes in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"
"item1":"stuff","item2":"12345","item3":0,"item4":"this uotes in it that need to be removed","item5":"some other stuff","item6":"","item7":"","item8":"a.b.c.d.e.f"

引用自：https://dba.stackexchange.com/questions/315995