Search and Replace in MySQL database with phpMyAdmin

Do a search and replace in any MySQL database in phpMyAdmin. You do not need to download the database export at all, just run this simple command. This also works through the MySQL command line.

To search and replace a text string, start up phpMyAdmin, and click on your database name that you want to run the search and replace through. At the top of the window, click on the "SQL" tab.

In the text box, enter the following code. This is the generic setup, so edit to satisfy your needs:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

You can add a WHERE clause onto this as well.

For example, here is one command a ran:

UPDATE `mos2_content` SET introtext = replace(introtext,"<p>","") WHERE `title` REGEXP '-0';

This got rid of all paragraph tags in the mos2_content table where the title included the string "-0".

Hope this helps. If you have any comments, suggestions, questions, or your own code, please submit a comment below.

Rate this Tutorial

Rate this Tutorial:

Current Rating:

4.5 out of 54.51/ 5.00 with 45 votes.

Current Comments

24 comments so far (post your own)

thanks a load!

saved me a few hours work!

Regards,

Adam

Posted by Adam Gardner (http://www.lumoslighting.co.uk)on Wednesday, 03.22.06 @ 12:15pm | #920

Saved me the tedious work of replacing 260 references by hand...!!!!!!!!

EXCELLENT and WELL RECOMMENDED.

Posted by Carlos on Thursday, 04.20.06 @ 05:16pm | #991

Thanks for that bunch of information i needed that and it helped me, thanks a lot :-)

Posted by vj_ (http://vj.e.pl)on Wednesday, 05.24.06 @ 06:02pm | #1082

Thanks for that bunch of information i needed that and it helped me, thanks a lot :-)

Posted by vj_ (http://vj.e.pl)on Wednesday, 05.24.06 @ 06:03pm | #1083

man this is GREAT!!!

Excellent job dude :)

Posted by johnnyfatboy on Thursday, 06.22.06 @ 01:22am | #2209

man this is GREAT!!!

Excellent job dude :)

Posted by johnnyfatboy on Thursday, 06.22.06 @ 01:23am | #2210

Wow --

thanks so much for posting this. You just saved me time and $$ -- 1827 rows were changed in 2 seconds!

Here's my code (just another example):

UPDATE `flexinode_data` SET textual_data = replace(textual_data,"/audio/","") WHERE `field_id` =41

This says:

Update the table called flexinode_date.
Set (choose) the textual_data row.
In the textual_data row find /audio/ and replace it with nothing, but only in the fields that are 41.

Cheers!

Posted by Catherine Murphy (http://www.charlesgatestudios.com)on Tuesday, 08.15.06 @ 07:22am | #2570

Wow --

thanks so much for posting this. You just saved me time and $$ -- 1827 rows were changed in 2 seconds!

Here's my code (just another example):

UPDATE `flexinode_data` SET textual_data = replace(textual_data,"/audio/","") WHERE `field_id` =41

This says:

Update the table called flexinode_date.
Set (choose) the textual_data row.
In the textual_data row find /audio/ and replace it with nothing, but only in the fields that are 41.

Cheers!

Posted by Catherine Murphy (http://www.charlesgatestudios.com)on Tuesday, 08.15.06 @ 07:23am | #2571

This is another "great" snippet going into my Dreamweaver. Been trying to update over 7000 entries from Princeton Universities Wordnet database, now it is a snap.

Thanks once again.

Posted by Olumide Otuyelu (http://ezpeezee.byethost6.com)on Sunday, 10.1.06 @ 10:40am | #2608

I found that this worked fine with a line or so of code. However, when I used this much code:

Learn How To Work At Home<a href="http://www.my website.com/folder/" target="_blank"> <font color="blue"><u>at our website</u></font></a>

It didn't do anything. I didn't get an error...it just said that nothing was replaced. Very strange...

Posted by tim (www.freepopupcreator.com)on Thursday, 11.16.06 @ 08:05pm | #2664

Tim,
You would have to escape all the symbols that MySQL/PHPMyAdmin treats as code, such as quotation marks.

For example:
href=" would have to written as
href=\"

-JC

Posted by Jazz (nope)on Monday, 02.12.07 @ 10:34am | #2762

How do i replace full URLs with this code? I am always getting an error when I post this:

UPDATE jos_content SET fulltext= replace(fulltext,"<a href="http://www.tkqlhce.com/click-2114936-10444716?sid=text&/;url=http://petcraze.com/advanced_search_result.php?sort=&/;vendors_id=11" target="_blank">","")/;

Just to let you know, I am a newbie to MySQL. I am trying to have the code replace an entire URL with nothing. Any advice is greatly appreciated

Posted by John (http://largedogbreedz.com)on Tuesday, 03.20.07 @ 10:14pm | #2800

How do i replace full URLs with this code? I am always getting an error when I post this:

UPDATE jos_content SET fulltext= replace(fulltext,"<a href="http://www.tkqlhce.com/click-2114936-10444716?sid=text&/;url=http://petcraze.com/advanced_search_result.php?sort=&/;vendors_id=11" target="_blank">","")/;

Just to let you know, I am a newbie to MySQL. I am trying to have the code replace an entire URL with nothing. Any advice is greatly appreciated

Posted by John (http://largedogbreedz.com)on Tuesday, 03.20.07 @ 10:14pm | #2801

How do i replace full URLs with this code? I am always getting an error when I post this:

UPDATE jos_content SET fulltext= replace(fulltext,"<a href="http://www.tkqlhce.com/click-2114936-10444716?sid=text&/;url=http://petcraze.com/advanced_search_result.php?sort=&/;vendors_id=11" target="_blank">","")/;

Just to let you know, I am a newbie to MySQL. I am trying to have the code replace an entire URL with nothing. Any advice is greatly appreciated

Posted by John (http://largedogbreedz.com)on Tuesday, 03.20.07 @ 10:15pm | #2802

A great bit of SQL that has saved me a lot of time...

Posted by Anon on Sunday, 04.22.07 @ 05:58am | #2857

Thanks a lot for this hint!!!!!!...

Perfect

Posted by Alp (www.i-cozum.com)on Monday, 04.30.07 @ 05:52am | #2861

Excellent, saved me a shedload of work too, was going to DL a 60mb sql file and use textwrangler to find replace then re import. Job done in a few minutes as opposed to an hour. Hats off

Posted by Simon on Friday, 06.8.07 @ 07:38am | #2916

Generic "you're amazing!" comment.

:) Thanks!

Posted by Marc (http://www.theofficequotes.com)on Friday, 08.24.07 @ 02:11pm | #2990

This advice did in 0.287 seconds what would have taken me 6 hours had I not read it. Brilliant, and thank you thank you thank you!!!

Posted by Tony (http://www.thumbtackpress.com)on Tuesday, 08.28.07 @ 02:06am | #2995

Found this again while trying to do several updates

Posted by olumide otuyelu (http://www.otuyelu.com)on Saturday, 10.27.07 @ 11:32am | #3063

HELP ME PLZ PLZ I DUNNO HOW TO DO IT

Posted by joshua (http://asiantown.net/got-rice-bitch)on Saturday, 01.26.08 @ 01:42pm | #3174

Thanks Man! Saved me a huge amount of time and sweats!

Posted by Jason (http://www.jayblogger.com)on Friday, 03.14.08 @ 03:02am | #3242

Brian!! I JUST LOVE YOU!!!

Posted by agel on Thursday, 03.20.08 @ 03:38am | #3254

This is exactly what I needed - thanks! :)

Posted by Jennifer (http://www.communitygala.com)on Friday, 04.4.08 @ 06:39pm | #3278

Leave your comment:

Learn how to add this comment form to your site with our comment form tutorial!

Name:

Email:

URL:

Comments:
 
Guess the letters and numbers (passphrase riddle) -- 2 chars before small G and then (((??? - 1) - 3) - 6) = -7 and not a "Y", but 'A' +2 letters and not a "Y", but 2 chars before L, small 'K' +1 letters , → retype that here
Enter the correct letters and numbers from the image into the text box. This small test serves as access restriction against malicious bots. Simply reload the page if this graphic is too hard to read.
 

Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted.

No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.