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.58/ 5.00 with 55 votes.

Current Comments

41 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

your page got me started.
after switching hosts, my database came back with some funky characters around bullets and trademark symbols and all sorts of goofy things

here is some quick easy code I came up with to walk through every table and field in the database to get rid of those things.
search_replace.php

Code:

@mysql_connect($hostname,$user,$password) or die("could not connect")/;
@mysql_select_db($database) or die("could not select")/;
$table_query="SHOW TABLES"/;
$table_result = mysql_query($table_query)/;
while($table_row = mysql_fetch_row($table_result)){
$table = $table_row[0]/;
echo "$table<br />"/;

$field_query = "SHOW COLUMNS FROM ".$table_row[0]/;
$field_result = mysql_query($field_query)/;
while($field_row = mysql_fetch_row($field_result)){
$field = $field_row[0]/;
echo "--$field<br />"/;
$search_query = "UPDATE $table SET $field = replace($field, 'Â','')"/;
echo "--..$search_query<br />"/;
mysql_query($search_query)/;
printf ("Updated records: %d<br />\r\n", mysql_affected_rows())/;
}

}


This will print out each step and how many records were affected, and could be easily modified to accept user input for the find and replace fields.

Posted by Dano (thekoop.com)on Wednesday, 07.9.08 @ 06:37pm | #3466

I tried this : UPDATE `nouvelles` SET texte = replace(texte,"’","'")

I got a sytax error message, I want to replace the ’ for '.

The content is endode as blob, could it be the problem ?

I also tried to replace – for -, no result, no row affected. But I know i have a lot of them in my text...

Posted by Frank Genoy on Tuesday, 08.19.08 @ 12:02pm | #3547

Excellent! Thank You so much. I have been using this for years but needed a solution to replace specific strings only when the strings where abbreviated. I didn't realise I could use the WHERE statement.

I used

mysql_query("update TABLE set COLUMN = replace(COLUMN,'Ko','Kodak') where prodbrand like 'Ko'") or die(mysql_error())/;

thus leaving the Correct entries unaffected.

Thanks again!

Posted by Shoes (http://www.01shoes.com/)on Friday, 08.22.08 @ 05:11am | #3548

Saved my time ^^

Posted by satikas (aerix.pri.ee)on Tuesday, 08.26.08 @ 12:08pm | #3559

Hi man,
I've got a problem with replacing in 'fulltext' .. in 'title' and in 'introtext' it works well .. but when I wanna do it in FULLTEXT, it gives me error no. 1064 ..

example:
doesn't work - UPDATE g08_content SET fulltext = replace(fulltext,"Carl","CARL")/;
works well - UPDATE g08_content SET introtext = replace(introtext,"Carl","CARL")/;

strage :/

could some one help me on my mail??
cheers .. matygol{*at*}seznam{*dot*}cz


can you help me

Posted by maty (http://www.gunners.cz/)on Wednesday, 08.27.08 @ 12:22am | #3560

Hi man,
I've got a problem with replacing in 'fulltext' .. in 'title' and in 'introtext' it works well .. but when I wanna do it in FULLTEXT, it gives me error no. 1064 ..

example:
doesn't work - UPDATE g08_content SET fulltext = replace(fulltext,"Carl","CARL")/;
works well - UPDATE g08_content SET introtext = replace(introtext,"Carl","CARL")/;

strage :/

could some one help me on my mail??
cheers .. matygol{*at*}seznam{*dot*}cz


can you help me

Posted by maty (http://www.gunners.cz/)on Wednesday, 08.27.08 @ 12:22am | #3561

Hi man,
I've got a problem with replacing in 'fulltext' .. in 'title' and in 'introtext' it works well .. but when I wanna do it in FULLTEXT, it gives me error no. 1064 ..

example:
doesn't work - UPDATE g08_content SET fulltext = replace(fulltext,"Carl","CARL")/;
works well - UPDATE g08_content SET introtext = replace(introtext,"Carl","CARL")/;

strage :/

could some one help me on my mail??
cheers .. matygol{*at*}seznam{*dot*}cz


can you help me

Posted by maty (http://www.gunners.cz/)on Wednesday, 08.27.08 @ 12:23am | #3562

Hi man,
I've got a problem with replacing in 'fulltext' .. in 'title' and in 'introtext' it works well .. but when I wanna do it in FULLTEXT, it gives me error no. 1064 ..

example:
doesn't work - UPDATE g08_content SET fulltext = replace(fulltext,"Carl","CARL")/;
works well - UPDATE g08_content SET introtext = replace(introtext,"Carl","CARL")/;

strage :/

could some one help me on my mail??
cheers .. matygol{*at*}seznam{*dot*}cz


can you help me

Posted by maty (http://www.gunners.cz/)on Wednesday, 08.27.08 @ 12:23am | #3563

Hi man,
I've got a problem with replacing in 'fulltext' .. in 'title' and in 'introtext' it works well .. but when I wanna do it in FULLTEXT, it gives me error no. 1064 ..

example:
doesn't work - UPDATE g08_content SET fulltext = replace(fulltext,"Carl","CARL")/;
works well - UPDATE g08_content SET introtext = replace(introtext,"Carl","CARL")/;

strage :/

could some one help me on my mail??
cheers .. matygol{*at*}seznam{*dot*}cz


can you help me

Posted by maty (http://www.gunners.cz/)on Wednesday, 08.27.08 @ 12:23am | #3564

Honestly. That is bad to the bone! Talk about saving me tons of time!

Posted by henderpender on Sunday, 08.31.08 @ 04:45pm | #3568

Thank you for sharing. F&R on any platform is such a time-saver.

Posted by Paul (http://www.is4profit.com)on Friday, 09.5.08 @ 05:08am | #3582

Thanks! At the risk of sounding like an echo: You saved me *a lot* of time.

Takk!

Posted by Thorri (hringbrot.is)on Tuesday, 09.16.08 @ 02:39am | #3613

48k rows all with a bad HTML tag and it only took 10 seconds to fix.

Posted by Air on Thursday, 09.18.08 @ 06:50am | #3619

Hi

UPDATE media SET url=REPLACE(url,'/usr/share/portal-gift/staff/..','/gift2')/;

In above quarry, search string '/usr/share/portal-gift/staff/..' is a variable value. How do I insert a variable value insted of fixed value hear?

Also please help me on how do i take the text in to variable before value '/media'

EX: /usr/share/portal/staff/../media/images/thumbnails/xyz.jpg

In above example i need to take all the values before '/media' it means i need to take in to variable , value /usr/share/portal/staff/..'

How do i perform these two tasks? please help me on this
Thanks.

Posted by samee on Monday, 09.22.08 @ 06:29am | #3633

Hi, I don't know anything of mysql ,yet, This is almost what I need.

I have a joomla install that I moved my database from other server. Everything worked good exept now I have serveral (Â)'s throught my site. how would I do a find replace on the whole database and not just spesific fields. Thanks

Posted by Grayden (baje.us)on Sunday, 09.28.08 @ 10:20pm | #3648

Can't tell you how much you helped me. You're my savior! Thank you so much!

Posted by KC on Wednesday, 10.29.08 @ 08:27pm | #3717

Thank you so much for this!!!

I used this to create the DB for my website (2500 pages) on my localhost, then change the URL field and upload it to my webhost. Great.

BTW, I'm using this free host with:

- 1500 MB of Disk Space
- 100 GB Bandwidth
- Your own domain hosting
- cPanel Control panel
- Website Builder
- Over 500 website templates ready for download
- Free POP3 Email Box and Webmail access
- FTP and Web based File Manager
- PHP, MySQL, Perl, CGI, Ruby.
- No Ads at all !

Check it out Here: http://www.000webhost.com/91892.html

Posted by RK (http://www.gurmatps.org)on Monday, 11.10.08 @ 07:44pm | #3762

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 J & _h_ ; 'J' +3 letters, _5_ , 'T', 0 chars before A; 'R' +3 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.