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.

以下查询显然会将A的所有实例替换为B

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屋！