How to search and replace on MYSQL?

Hi guys,

Does someone know how to search & replace all instances of a word in the MYSQL using PHP My Admin (I believe)?

I am having problems cleaning out my MYSQL database (Wordpress) of a old url and replace with another....its giving me a headache...cos there is 1300 instances of it...in the databse and this old url is screwing alot of things up

Thanks for any answers

 

Does this help?

UPDATE TABLENAME SET FIELDNAME = 'NEWVALUE' WHERE (FIELDNAME = 'OLDVALUE')

 

The above would work if you want to replace the whole string. If you just want to replace a portion of the string, you can use the function REPLACE().

e.g.:
UPDATE tablename SET fieldname = REPLACE(fieldname, 'http://www.oldurl.com', 'http://www.newurl.com');

This query will update 'http://www.oldurl.com/foo/index.html' to 'http://www.newurl.com/foo/index.html'.

 

I am trying a simpler way that I think will work.

Go to the export tool and export the database so that you have it in a file that will be something like databasename.mysql

Save a copy for back up purposes, then make a new copy which you will edit.

Then take that new file you just saved, open it up in notepad, do a search and replace through the whole file, then import the database using the import tool.

If you messed something up, you can still import the old database again.

I am trying this right now because I have a similar problem. If this doesn't work, I'll let you know

 

What? Just use replace as mentioned above. How is your way easier??

 

Just use update command of SQl to replace items in the table

 

thebigsteveman, were you successful in doing your global replace?

 

The first reply to your query should have solved your problem?