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

This should actually be what you want in MySQL:

UPDATE wp_postSET 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

Alternatively, you could use the functions LOCATE(), INSERT() and CHAR_LENGTH() like this:

INSERT(originalvalue, LOCATE('A', originalvalue), CHAR_LENGTH('A'), 'B')

Full query:

UPDATE wp_postsSET post_content = INSERT(originalvalue, LOCATE('A', originalvalue), CHAR_LENGTH('A'), 'B');

With reference to https://dba.stackexchange.com/a/43919/200937 here is another solution:

UPDATE wp_posts SET post_content = CONCAT( LEFT(post_content , INSTR(post_content , 'A') -1),                           'B',                           SUBSTRING(post_content, INSTR(post_content , 'A') +1))WHERE INSTR(post_content , 'A') > 0;

If you have another string, e.g. testing then you need to change the +1 above to the according string length. We can use LENGTH() for this purpose. By the way, leave the -1 untouched.

Example: Replace "testing" with "whatever":

UPDATE wp_posts SET post_content = CONCAT( LEFT(post_content , INSTR(post_content , 'testing') -1),                           'whatever',                           SUBSTRING(post_content, INSTR(post_content , 'testing') + LENGTH("testing"))WHERE INSTR(post_content , 'testing') > 0;

By the way, helpful to see how many rows will be effected:

SELECT COUNT(*)FROM post_content WHERE INSTR(post_content, 'A') > 0;