Commands to search and replace in mysql table

[Fli]

Hi, sometimes we need to replace some string in mysql by another string. What command to use?

You may try this command to Preview matching columns:

SELECT REPLACE(mycolumn, 'oldstring', 'newstring') FROM tablename

After preview, you can do search and replace in mysql (canot be reverted, do full mysql backup first):

UPDATE REPLACE(mycolumn, 'oldstring', 'newstring') FROM tablename

When you need to replace partially (part of the word, or part of the string, then you can use LIKE %. The source of this info is a thread how to bulk delete/replace in overspammed PHPBB forum mysql.

update TableName set ColumnName = REPLACE(ColumnName, "']", '"]') WHERE ColumnName LIKE "%']%";

(Again this will permanently delete in your mysql, so backup it first)

How to delete all entries which contain partial string?

DELETE FROM `yourmysqltable` WHERE (`yourcolumnname` LIKE '%partialstring%') LIMIT 20000;

(Again this will permanently delete in your mysql, so backup it first)

[Fli]

"']" - means im trying to replace ']
"%']%" - means im trying to find it "in phrasse" like: phrasse']phrasse