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.
Current Comments
55 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
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
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
You are life savor!
thanks alot
Posted by John Conor (http://www.ticket-directory.com)on Tuesday, 12.30.08 @ 02:41am | #3875
The use of your update statements saves a lot of time.
But they are not "allrounders". The problem of 'maty' (see above) wouldn't have been arised, if the column names would have been enclosed in backquotes:
UPDATE `mos2_content` SET `introtext` = replace(`introtext`,"<p>","") WHERE `title` REGEXP '-0'/;
This is, because "fulltext" is a keyword in MySQL. Thatsway the parser of MySQL handles "fulltext" in another way than "introtext". The way with backquotes works for keywords and all others also.
Regards
Thomas - Segelfilmer
Posted by Segelfilmer (http://www.segelfilmer.de)on Wednesday, 03.11.09 @ 10:22am | #4068
Thanks a ton... when transferring a client's site from the beta testing domain to the live www URL, I could quickly alter all the absoulte URLs that the CMS created in the Db. Much appreciated.
Posted by PJaxon (http://www.internextdesigns.com)on Thursday, 04.9.09 @ 12:21pm | #4245
Thanks a ton... when transferring a client's site from the beta testing domain to the live www URL, I could quickly alter all the absoulte URLs that the CMS created in the Db. Much appreciated.
Posted by PJaxon (http://www.internextdesigns.com)on Thursday, 04.9.09 @ 12:21pm | #4246
Would be wonderful if I could actually read the code snippet that's hidden underneath the ads.
Posted by Lucas on Thursday, 05.7.09 @ 01:01am | #4359
hey guys...
I want to update my joomla cotnent with another column data
eg.
mysql_query("update jos_content set introtext = replace(introtext,'XXXXXXXX','DATA FROM ANOTHER COLUMN') where SECID = '1' or die(mysql_error())//;
DATA FROM ANOTHER COLUMN == SN column from same table.....
cAN YOU PLEASE HELP ME IN CORRECT SQL SYNTAX
Posted by Arpit Deomurari (www.wildone.in)on Tuesday, 05.12.09 @ 11:38pm | #4439
hey guys...
I want to update my joomla cotnent with another column data
eg.
mysql_query("update jos_content set introtext = replace(introtext,'XXXXXXXX','DATA FROM ANOTHER COLUMN') where SECID = '1' or die(mysql_error())//;
DATA FROM ANOTHER COLUMN == SN column from same table.....
cAN YOU PLEASE HELP ME IN CORRECT SQL SYNTAX
Posted by Arpit Deomurari (www.wildone.in)on Tuesday, 05.12.09 @ 11:39pm | #4440
As a thank you to @wpanswers, sharing what worked for me. I needed to search and replace an ID in over 200 posts. Here's the code:
UPDATE `wp_posts` SET post_content = replace(post_content,"Old_ID","New_ID")/;
Thank you!
Posted by Meryl K. Evans (http://www.meryl.net/blog)on Monday, 05.18.09 @ 06:53pm | #4479
1) The Google Ad covers the code snippet - I had to view source to see the full code
2) Too much spam here.
Posted by Paul (http://paulmackenzieross.com)on Wednesday, 06.17.09 @ 05:44am | #4885
thank you this was helpful
Posted by predicaciones (http://iglesiagetsemani.net/predicaciones-sermones/)on Wednesday, 07.29.09 @ 04:15pm | #5920
hi folk,
This is the perfect place which was I seeking for..
And the formula works 100% perfect to find and replace a specific string in a Mysql Database table.
==========================================
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]')/;
==============================================
Thanks,
Regards,
Sachin Pethani(Web-Farmer)
www.letsnurture.com
Posted by letsnurture (http://www.letsnurture.com)on Tuesday, 09.22.09 @ 03:43am | #7290
hi folk,
This is the perfect place which was I seeking for..
And the formula works 100% perfect to find and replace a specific string in a Mysql Database table.
==========================================
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]')/;
==============================================
Thanks,
Regards,
Sachin Pethani(Web-Farmer)
www.letsnurture.com
Posted by letsnurture (http://www.letsnurture.com)on Tuesday, 09.22.09 @ 03:43am | #7291
hi folk,
This is the perfect place which was I seeking for..
And the formula works 100% perfect to find and replace a specific string in a Mysql Database table.
==========================================
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]')/;
==============================================
Thanks,
Regards,
Sachin Pethani(Web-Farmer)
www.letsnurture.com
Posted by letsnurture (http://www.letsnurture.com)on Tuesday, 09.22.09 @ 03:43am | #7292
Great tip - saved me lots of time!!
Posted by Matt (www.baradesign.com.au)on Wednesday, 09.23.09 @ 10:23pm | #7359
Thanks for the great tips.
I have a question and I greatly appreciate your help.
I need to clean up certain bbcode font tags and I have been trying for hours to no avail. I am trying to understand how wildcard works in mysql but it seems complicated so far at least in my case.
What I need is to find all instances of and delete the all. Arial here is just an example but there are other types involved too. I will then have to find and delete all closing tags . I tried this but didnt work:
UPDATE table_name SET table_field = replace(table_field,"[font=","") WHERE table_field like '[font=%]'
Any help is greatly appreciated.
Posted by Jay on Wednesday, 10.7.09 @ 12:20pm | #7647
isnt it better to use a dext editor on the dump?
Posted by dentist (http://www.cosmeticdentistryguide.co.uk)on Friday, 12.11.09 @ 07:32pm | #9678
Thanks very much, this is EXTREMELY useful when switching domain names, when your old domain name is plastered all over the place in a database /;)
Posted by Xamuel (http://www.xamuel.com)on Monday, 01.25.10 @ 06:50am | #10520
replica watches<BR>watches replica<BR>cheap fake watches<BR>[url=http://www.patekphilippewatches.us/Rolex/Sea%20Dweller/]Rolex Sea-Dweller watches[/url]<BR>[url=http://www.patekphilippewatches.us/Rolex/Day%20Date%20II/]replica Rolex Day-Date II[/url]<BR>[url=http://www.patekphilippewatches.us/Bell-&/;-Ross/]replica Bell &/; Ross[/url]<BR>Air Jordan Fusion<BR>nike air jordan<BR>Tiffany&/;Co Titanium I.D. Necklace<BR>
Posted by Tiffany Necklaces fake (http://www.mycostly.c)on Friday, 01.29.10 @ 01:10am | #10635
The texts just about this good post are presented by the paper writing service and people will <a href=" http://www.gogetessays.com">buy essays</a> or custom written essays about that.
Posted by Lily26 (http://www.gogetessays.com)on Friday, 01.29.10 @ 07:10am | #10671
Rate this Tutorial
Current Rating: