Fli
08-08-2013, 11:49 AM
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 (http://tophostingforum.com/how-to-clean-overspammed-phpbb-forum-t1783.html#p4551).
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)
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 (http://tophostingforum.com/how-to-clean-overspammed-phpbb-forum-t1783.html#p4551).
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)