删除或替换错误的字符串

I have a sql dump with incorrect URLS.

Text stuff goes here before the url <a href="http://www.incorrectsite.com/incorrecturl/2000/xxx/yyy/zzz.html>Correct Text</a>

In the correct urls

Another text stuff goes here. <a href="http://www.correctsite.com/correctsite/uploads/2000/xxx/yyy/zzz.html>Correct Text</a>. And here too.

I am required to remove incorrect url and clean up the SQL dump.

I can find the records using,

select * from tbl_incorrect WHERE like '%incorrecturl%'

Each returning value for the URLs are different from each other.

Any thoughts?

You can open dump into vim and replace all URLs:

:%s/www.incorrectsite.com\/incorrecturl/www.correctsite.com\/correctsite\/uploads/g
:wq

or use sed:

sed 's/www.incorrectsite.com\/incorrecturl/www.correctsite.com\/correctsite\/uploads/g' incorrect.sql > correct.sql

There is MySQL's native REPLACE() method. http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_replace

If you have to go through an entire table of records, then you could use a stored procedure with a CURSOR.

If you have a text version of the dump, you could also utilize find and replace feature, like in unix you could utilize 'sed' to regex and replace within the SQL dump file.

Use replace

UPDATE `table_name` SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text') where field_name like = "%unwanted text%"