mysql - SQL: search/replace but only the first time a value appears in record

Question

I have html content in the post_content column.

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.

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

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

Answer 1

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));

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.

See my test below:

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;

Produces:

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