Search and Replace in MySQL database with phpMyAdmin

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