MySQL : replace occurence of a string in field exc

Question

I want to update all fields of a column, a lot of them have a desired string in there, but I want this string to be in only each field once, for instance : "MyString OtherString MyString AnotherString AndAnother MyString"

to

"MyString OtherString AnotherString AndAnother"

would you have any idea on how to achieve this ?

Answer 1

If "MyString" will always occur as the first term in the field, this would work:

update MyTable set MyField = replace(MyField, ' MyString','')

The key point above is that we look for occurrences of "MyString" with a leading space, so the first occurrence at the beginning of the field will be ignored.

However, my guess is this might be too fragile - what if the first occurrence of "MyString" is not at the beginning of the field?

in this latter case you need the following:

UPDATE 
    MyTable 
SET 
    MyField = 
    CONCAT(
        LEFT(MyField,INSTR(MyField,'MyString') + LENGTH('MyString')), 
        REPLACE(RIGHT(MyField, LENGTH(MyField) - (INSTR(MyField,'MyString') + LENGTH('MyString'))), 'MyString','') 
    )

What this does is to split the field into two, the first part up to and including the first occurrence of "MyString", and the second part replacing all further occurrences of it.