SQL:搜索/替换，但只有第一次出现在记录中的值 [英] SQL: search/replace but only the first time a value appears in record

问题描述

我在 post_content 列中有 html 内容.

I have html content in the post_content column.

我想搜索 A 并将其替换为 B，但只有第一次 A 出现在记录中，因为它可能出现多次.

I want to search and replace A with B but only the first time A appears in the record as it may appear more than once.

下面的查询显然会用 B 替换 A 的所有实例

The below query would obviously replace all instances of A with B

UPDATE wp_posts SET post_content = REPLACE(post_content, 'A', 'B');

推荐答案

这实际上应该是你在 MySQL 中想要的:

This should actually be what you want in MySQL:

UPDATE wp_post
SET post_content = CONCAT(REPLACE(LEFT(post_content, INSTR(post_content, 'A')), 'A', 'B'), SUBSTRING(post_content, INSTR(post_content, 'A') + 1));

这比我之前的回答稍微复杂 - 您需要找到A"的第一个实例(使用 INSTR 函数)，然后使用 LEFT 与 REPLACE 结合使用来替换该实例，而不是使用 SUBSTRING 和 INSTR找到您要替换的同一个 'A' 并用前一个字符串连接它.

It's slightly more complicated than my earlier answer - You need to find the first instance of the 'A' (using the INSTR function), then use LEFT in combination with REPLACE to replace just that instance, than use SUBSTRING and INSTR to find that same 'A' you're replacing and CONCAT it with the previous string.

见下面我的测试:

SET @string = 'this is A string with A replace and An Answer';
SELECT @string as actual_string
, CONCAT(REPLACE(LEFT(@string, INSTR(@string, 'A')), 'A', 'B'), SUBSTRING(@string, INSTR(@string, 'A') + 1)) as new_string;

产生:

actual_string                                  new_string
---------------------------------------------  ---------------------------------------------
this is A string with A replace and An Answer  this is B string with A replace and An Answer

这篇关于SQL:搜索/替换，但只有第一次出现在记录中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！