主页 / dba / 问题 / 315995
Accepted
IGGt
Asked: 2022-08-25 03:48:28 +0800 CST

MySQL 从字符串中间删除引号，同时保持字符串的其余部分完好无损

  • 772

我的表中有一些字段包含类似于以下的数据：

{"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使用修改后的数据对原始数据执行操作？

mysql mysql-5.7

1 个回答

  1. Best Answer

    一种选择是更新列。所有提到的步骤都可以在这个小提琴上找到，在使用建议的生产步骤之前，请始终使用测试环境。

    假设我们有下表：

    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"
    • 1

相关问题