MySql -如何更新字符串的一部分?

I'm looking for a way to update just a portion of a string via MySQL query.

我正在寻找一种方法来通过MySQL查询更新字符串的一部分。

For example, if I have 10 records all containing 'string' as part of the field value (i.e., 'something/string', 'something/stringlookhere', 'something/string/etcetera', is there a way to change 'string' to 'anothervalue' for each row via one query, so that the result is 'something/anothervalue', 'something/anothervaluelookhere', 'something/string/etcetera', is there a way to change 'anothervalue'

例如，如果我有10条记录，都包含'string'作为字段值的一部分(例如。， 'something/string'， 'something/stringlookhere'， 'something/string/etc '，是否有办法通过一个查询将每一行的'string'改为'anothervalue'，结果是'something/anothervalue'， 'something/anothervaluelookhere'， 'something/string/ string/etc '，是否有办法改变'anothervalue'

3 个解决方案

#1

180

I think this should work:

我认为这应该奏效:

UPDATE table SET field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%';

#2

UPDATE `table` SET `field` = REPLACE(`field`, 'string', 'anothervalue')

#3

Use the LIKE operator to find the rows that you care about and update them using the REPLACE function.

使用LIKE操作符查找您关心的行并使用REPLACE函数更新它们。

For example:

例如:

UPDATE table_name SET field_name = REPLACE(field_name,'search','replace') WHERE field_name LIKE '%some_value%'

#1

180