MySQL:替换字段中出现的字符串，但第一个除外 [英] MySQL : replace occurence of a string in field except first one

问题描述

我想更新一列的所有字段，其中很多字段都有一个想要的字符串，但我希望这个字符串只在每个字段中出现一次，例如:"MyString OtherString MyString AnotherString AndAnother MyString"

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"

到

"MyString OtherString AnotherString AndAnother"

"MyString OtherString AnotherString AndAnother"

您对如何实现这一目标有任何想法吗?

would you have any idea on how to achieve this ?

推荐答案

如果MyString"总是作为该领域的第一个术语出现，这将起作用:

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

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

上面的关键点是我们寻找带有前导空格的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.

但是，我的猜测是这可能太脆弱了 - 如果MyString"的第一次出现不在字段的开头怎么办?

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

这样做是将字段分成两部分，第一部分直到并包括第一次出现的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.

这篇关于MySQL:替换字段中出现的字符串，但第一个除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！