MySQL 5.5中替换复合字符串指定第五位元素的方法求助

需求场景 #

需要将竖线分隔的复合字符串中的第五个元素替换为指定值，例如把字符串"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"中的"CAR"替换为"FOO"，最终得到"NORMAN|BATES|RO|Eserc|FOO|(Ex: )"。但之前尝试的SELECT查询仅返回"FOO"，UPDATE语句会将整个Ub字段值替换为"FOO"，需要修正SQL逻辑。

错误分析 #

你之前的语句错误地通过SUBSTRING_INDEX定位到了字符串的第二个元素，随后仅对该元素执行替换操作，导致结果只返回替换后的单个元素，UPDATE时直接覆盖了整个字段值，完全不符合需求。

正确解决方案 #

1. 测试用SELECT语句

通过拆分字符串为前4个元素、替换后的第五个元素、第五个元素之后的所有内容三部分，再拼接得到完整结果：

SELECT
  CONCAT(
    SUBSTRING_INDEX("NORMAN|BATES|RO|Eserc|CAR|(Ex: )", "|", 4),
    "|FOO",
    SUBSTRING("NORMAN|BATES|RO|Eserc|CAR|(Ex: )", LENGTH(SUBSTRING_INDEX("NORMAN|BATES|RO|Eserc|CAR|(Ex: )", "|", 5)) + 1)
  ) AS updated_str;

执行后将返回目标字符串：NORMAN|BATES|RO|Eserc|FOO|(Ex: )

2. 实际UPDATE语句

将常量字符串替换为目标字段Ub，即可实现批量更新：

UPDATE `tbl_d`
SET Ub = CONCAT(
  SUBSTRING_INDEX(Ub, "|", 4),
  "|FOO",
  SUBSTRING(Ub, LENGTH(SUBSTRING_INDEX(Ub, "|", 5)) + 1)
)
WHERE Cod = "1642";

逻辑说明 #

• SUBSTRING_INDEX(Ub, "|", 4)：提取字符串中前4个元素组成的部分（不含第五个元素的分隔符）
• "|FOO"：拼接替换后的第五个元素，前面添加分隔符保证格式一致
• SUBSTRING(Ub, LENGTH(SUBSTRING_INDEX(Ub, "|", 5)) + 1)：计算前5个元素的总长度，从该长度+1的位置开始截取，获取第五个元素之后的所有内容（包括后续的分隔符和元素）

内容的提问来源于stack exchange，提问作者George A. Custer