SQL: search/replace but only the first time a valu

问题:

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

回答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

回答2:

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_posts
SET post_content = INSERT(originalvalue, LOCATE('A', originalvalue), CHAR_LENGTH('A'), 'B');

回答3:

If you are using an Oracle DB, you should be able to write something like :

UPDATE wp_posts SET post_content = regexp_replace(post_content,'A','B',1,1)

See here for more informations : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

Note : you really should take care of post_content regarding security issue since it seems to be an user input.

回答4:

Greg Reda's solution did not work for me on strings longer than 1 character because of how the REPLACE() was written (only replacing the first character of the string to be replaced). Here is a solution that I believe is more complete and covers every use case of the problem when defined as How do I replace the first occurrence of "String A" with "String B" in "String C"?

CONCAT(LEFT(buycraft, INSTR(buycraft, 'blah') - 1), '', SUBSTRING(buycraft FROM INSTR(buycraft, 'blah') + CHAR_LENGTH('blah')))

This assumes that you are sure that the entry ALREADY CONTAINS THE STRING TO BE REPLACED! If you try replacing 'dog' with 'cat' in the string 'pupper', it will give you 'per', which is not what you want. Here is a query that handles that by first checking to see if the string to be replaced exists in the full string:

IF(INSTR(buycraft, 'blah') <> 0, CONCAT(LEFT(buycraft, INSTR(buycraft, 'blah') - 1), '', SUBSTRING(buycraft FROM INSTR(buycraft, 'blah') + CHAR_LENGTH('blah'))), buycraft)

The specific use case here is replacing the first instance of 'blah' inside column 'buycraft' with an empty string ''. I think a pretty intuitive and natural solution:

1. Find the index of the first occurrence of the string that is to be replaced.
2. Get everything to the left of that, not including the index itself (thus '-1').
3. Concatenate that with whatever you are replacing the original string with.
4. Calculate the ending index of the part of the string that is being replaced. This is easily done by finding the index of the first occurrence again, and adding the length of the replaced string. This will give you the index of the first char after the original string
5. Concatenate the substring starting at the ending index of the string

An example walkthrough of replacing "pupper" in "lil_puppers_yay" with 'dog':

1. Index of 'pupper' is 5.
2. Get left of 5-1 = 4. So indexes 1-4, which is 'lil_'
3. Concatenate 'dog' for 'lil_dog'
4. Calculate the ending index. Start index is 5, and 5 + length of 'pupper' = 11. Note that index 11 refers to 's'.
5. Concatenate the substring starting at the ending index, which is 's_yay', to get 'lil_dogs_yay'.

All done!

Note: SQL has 1-indexed strings (as an SQL beginner, I didn't know this before I figured this problem out). Also, SQL LEFT and SUBSTRING seem to work with invalid indexes the ideal way (adjusting it to either the beginning or end of the string), which is super convenient for a beginner SQLer like me :P

Another Note: I'm a total beginner at SQL and this is pretty much the hardest query I've ever written, so there may be some inefficiencies. It gets the job done accurately though.

回答5:

To keep the sample of gjreda a bit more simple use this:

UPDATE wp_post
    SET post_content =
            CONCAT(
                REPLACE(LEFT(post_content, 1), 'A', 'B'),
                SUBSTRING(post_content, 2)
            ) 
    WHERE post_content LIKE 'A%';