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"并将其与前一个字符串 CONCAT.

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