Skip navigation

Search and Replace in WordPress MySQL Database

There are times when you must enter the WordPress MySQL database to search and replace some content. If you don’t have to do this, then DO NOT DO THIS. If you have to do this, then your head should be examined carefully, opened up and inspected for programming errors, and then you may proceed with due and dilligent care.

When might this insanity occur? This overwhelming need to destroy your blog? Most commonly it happens when you change domain or email addresses and need to make sure that all the old links are gone, replaced with the new links. Or if you have just imported massive content into the database and you’ve discovered a recurring boo boo across the majority of posts.

Reason why NOT to do this. First, because it can and will destroy your blog and database if used incorrectly. Second, if the item you wish to search and replace is found in 20 or fewer posts, fix them manually. Much less invasive and destructive.

Third, what you search for MUST be unique. It must only be found at the point where you want the change made. Searching for a domain name or email address is pretty unique content, but a group of words or code that can be found in other places will search and replace ALL of the code, screwing up everything. For example, if you search and replace every “there” for a “their”, you will end up with “their’s no place like home” or “theirfore”. I once made the mistake of deciding that “website” was to be spelled consistently on my site as “web site”. A massive search and replace turned links with the word “website” into “web%20site” which broke all such links. Do you know how many people have the word “website” in their links? Too many, and I think I linked to all of them.

If you are still willing to go forward with this amazingly stupid and risky thing to do, here are the final warnings and instructions.

BE VERY CAREFUL WITH THIS. IT WILL HURT.

Should I say, “don’t do this at home”, too? You get the point.

1. BACKUP YOUR DATABASE FIRST.

2. From within PHPMyAdmin, open your database.

3. Check the names of the table and field holding the information you want to change. For WordPress users, the most common search and replace is done on the content so we’ll use the wp-posts table and the post-content field within that table.

4. To search and replace text within your wp-posts table and the post-content field, click on the SQL tab and enter the following with the exact item you want to search for in the third line, and what you want to replace it with in the last line.

UPDATE wp_posts SET post_content = REPLACE (
post_content,
'Item to replace here',
'Replacement text here');

5. Making sure that the ONLY parts you change are within the little single quotes.

6. Make sure EVERYTHING is spelled right and that the names of wp-posts and post_content, or whatever table and fields you are using, match the table and field within your database. Make sure that post_content is listed twice and that they match.

4. When you are sure, and you have made arrangements to have your body and brain donated to science when your brain explodes because this destroys everything in your database (okay, not really, but I’m impressing upon you the care you have to take with this – got it? Can I stop now? Good.), then click the GO or APPLY or whatever the DO IT NOW button looks like in your version of PHPMyAdmin.

5. It will then go through your database’s wp_posts table in the post_content field looking for the first line in the search and replace command, and replacing it with the second line.

6. The results will tell you how many records were changed. Let’s hope it’s the number you anticipated. If not, then restore your backup and start over.

While I jest about this process, it is a very serious and risky thing to do, but it is also very helpful. When setting up a test WordPress site, I often want all the pings closed. When I move a test site out to the public, pings need to be turned back on. To turn all the pings back on for every post and Page within my site, I used the following shortcut version of the above search and replace command:

UPDATE wp_posts SET ping_status="open";

If the ping_status was “closed”, it was now “open” and if it was blank, it was also now “open”. I then went through the Pages for my “About”, “Contact”, and other posts and Pages I didn’t want open to comments or pings and turned them off manually.

Again, I never do this without a backup, and even I have screwed this up, so if I can do it, I’m sure you will, too. ;-)

87 Comments

  1. Michael Hessling
    Posted January 10, 2006 at 3:10 pm | Permalink

    This post is very useful. However, is there any way to grab text like:

    onclick="window.open('/photo.html?/wp-photos/photo123.jpg','','resizeable=1,width=500,height=450'); return false"

    where “photo123.jpg” changes from post to post?

    I don’t relish the thought of going through 500+ posts and stripping out the bad javascript. :(

    • Tracy Carpenter
      Posted April 22, 2013 at 3:17 pm | Permalink

      What I do is replace before the image with an html opening comment, and replace what is after it with a close. So, you’d be left with or less. ;)

    • Posted April 22, 2013 at 3:59 pm | Permalink

      If I understand you, you wish to change an image in your content that is at the beginning of a post or every post? Everything in my article apples but if you just need to change one image, edit that post. If the image is within the web design, specifically the WordPress Theme, you will need to change it with a Child Theme.

      Sorry, without more specific information, I can’t help you with your search and replace issues. Thanks.

  2. Posted January 10, 2006 at 5:03 pm | Permalink

    If the image file name is in the javascript, you are screwed. Yes, you can delete the redundant javascript, but the parts with the image file that is different for every reference, you are out of luck.

    I’m sure there is someone out there, not me, that would help you create a list of the images and write a script that would search and replace the javascript, inserting each file name as it goes, but that’s beyond me.

    Been there, done that, too many times, so unless you have a seriously swift PHP scripting buddy, get to work. It takes much longer to whine about it then to manually fix it. Trust me. I’m an expert on whining over botched code. ;-)

  3. Michael Hessling
    Posted January 12, 2006 at 11:26 am | Permalink

    So … use PHP to write an if-else loop? I’ll do that; that’s more interesting than either whining or the manual fix. :)

  4. Shawna
    Posted February 17, 2006 at 5:55 pm | Permalink

    I’m having a bit of a problem attempting to replace something. It’s actually with a PHPBB table. I have random  ’s all over 200+ posts, but phpMyAdmin isn’t liking the & in the command. It will work if I just use  , but that leaves me with &’s all over the place.

    Is there a workaround for this?

    This is what I’m trying:
    UPDATE gecko_fl_phpbb_posts_text SET post_text = REPLACE (post_text,” ”,””);

  5. Posted February 17, 2006 at 7:43 pm | Permalink

    I’m not sure what the correct replacement for non-breaking spaces would be. You would think that that   code would do it. I don’t understand why it leaves ampersands behind. Sorry. I’m not even close to a MySQL expert. Keep looking and if you find a solution, would you let me know?

  6. Posted February 24, 2006 at 10:39 am | Permalink

    After looking through the actual posts saved in the tables, I realized that when I was converting it had replaced the actual ampersand on the   with &. Like this: & amp;nbsp. Then I did a search and replace on that and it fixed everything :)

  7. Posted February 24, 2006 at 1:32 pm | Permalink

    It got it. ;-) And thanks for the tip! Character entities are a pain.

  8. Posted November 18, 2006 at 4:16 pm | Permalink

    Thanks for the post. It helped me a bunch with a silly string that got stuck in my data on export. I used it on MT, but it worked well.

    Thanks

  9. Posted August 6, 2007 at 7:12 am | Permalink

    Damn that was easy! Thanks for the morons guide to modifying a WordPress database :)

  10. Posted October 5, 2007 at 12:47 pm | Permalink

    mainly posting this for myself, so i don’t forget: some versions require backticks around the table_field you’re replacing. my query is like:

    UPDATE wp_posts SET `guid` = REPLACE (
    `guid`,
    'http://old.domain',
    'http://new.domain');

  11. Posted October 5, 2007 at 1:28 pm | Permalink

    They aren’t “tickbacks” but apostrophes. Backticks or “ticks” are character codes created in word processing programs from the key found next to the 1 on English keyboards. They are not recognized elsewhere, so please only use quote marks and apostrophes. Ticks might bork your code.

    The use of quotes around these areas must be new for new versions of MySQL…checking…nope. The “field” which is the first variable, does not have to be in apostrophes. So the correct layout is mentioned about, and using your example, it should be:

    UPDATE wp_posts SET guid = REPLACE (
    guid,
    'http://old.domain',
    'http://new.domain');

    Where did you figure it out that it needed apostrophe marks around the field name?

  12. Posted October 5, 2007 at 4:12 pm | Permalink

    that was in PHPMyAdmin, so maybe it’s specific to that. and no, those are backticks. it requires backticks on the table_field, and apostrophes on the values.

  13. Posted October 6, 2007 at 10:21 am | Permalink

    Really odd, cuz I’ve been using this for years in PHPMyAdmin, and have never used backticks or apostrophes on field table names. I’d love to know where you found that as my research continues to show that it isn’t necessary. Glad it works for you, and I’ll keep looking for support documentation on this. Very odd.

  14. Posted October 8, 2007 at 10:47 am | Permalink

    i “found” it, because when i ran the command without the backticks, it kept replacing old.domain with guid. The version of PMA (2.8.1, part of XAMPP) sometimes has a little “fields” suggestion box in the top right. clicking on those fields added the backticks. when i ran the query with backticks, it replaced with the proper values.

  15. marv
    Posted October 19, 2007 at 10:37 pm | Permalink

    Thank you, for the sql update query and for the incessant cautions.

  16. Posted October 20, 2007 at 8:36 am | Permalink

    I’ve been looking for a safe way to do this for a while!

    Now I can batch edit my anime videos on my site with ease.

  17. Posted December 23, 2007 at 9:32 pm | Permalink

    Thanks Lorelle :)
    I hunted high and low for how to do this and none of the solutions I found worked at all. But I could distinctly remember reading about this very topic on here so did a search for “lorelle mysql search and replace” and low and behold I found the original post and sure enough it worked perfect :)

  18. Posted January 7, 2008 at 11:04 am | Permalink

    I changed my theme so backgrounds are blue instead of white, and I embedded adsense with a white background into the middle of each post.

    Adsense has since come out with a better mechanism so I can make changes inside their interface and the colors will auto-update. However I have over 600 posts to swap the old adsense code out for the new.

    My find and replace will involve the fairly large chunk of adsense code. Two questions, one is I know to escape the periods in the find/old code, but do I need to escape the periods in the replace/new code also? Also, do you think I will have better or worse luck in my search/replace operation because I am searching for such a large and unique chunk of code?

  19. Posted January 7, 2008 at 12:26 pm | Permalink

    @ Joe Vandal:

    You know there are Plugins that will help you put your ads in your posts and blog without doing this manually? Who Sees Ads WordPress Plugin by PlanetOzh is also very popular.

    As for your question, search and replace what needs to be changed. Leave what doesn’t. Larger, UNIQUE chunks are better than small, not unique chunks, but size doesn’t matter. Uniqueness does. You don’t want to search and replace things that will mess up other areas in your database. BACKUP first. This is not to be done lightly.

    I’d replace all of the code with a Plugin so you don’t have to do this again.

    As for ads within blog posts, that’s another issue and most people find it annoying and that it biases them against the blog. Just information you might want to know.

  20. Posted February 8, 2008 at 6:45 pm | Permalink

    thanks!! very usefull for my blog… u’re my hero!!

  21. Posted February 13, 2008 at 1:10 am | Permalink

    Lorelle – Love your blog – THANKS! I amsol and struggling. I have a blog that is remorelty hosted, Oploaded the files to godaddy for me and made some changes to the blog. Worked fine. I then tried to copy it to another url at godaddy. I followed instructions re FTP’ing the files to my hardrove and made 1 mistake – I never backed up the database befopre changing the URL. Of course the moment I changed the URL – I could no longer get into wordpress or the blog. I got all the files and the changeed database into the new locatiobn and restpored everything to the old one. Now I cannot get to either blog. I have checked and changed the URL in the database and the config file. This is the message I get:
    “Error establishing a database connection”

    I have checked these and they seem to be fine. What options can you think of and is there anything I cando? Thanks.

  22. Posted February 13, 2008 at 5:59 pm | Permalink

    @ Gary:

    Ouch. If you can edit the database, go in and manually change the information. After that, your best help on this is the WordPress Support Forums. They will be able to help you with the specifics.

    Also, I deleted the error information that revealed private information about your blog’s server. DO NOT publish that information anywhere as it can invite hackers.

  23. Posted April 5, 2008 at 3:26 pm | Permalink

    Thanks Lorelle! This is so easy (if you’re used to working with databases) but you saved me soooo much time in figuring out the statement. Thanks!! :)

  24. Mad Dog
    Posted July 11, 2008 at 10:15 am | Permalink

    Have you heard of a plug-in that will strip out specified HTML code tags from posts? I set up a blog for a client who, despite my instructions, created lots of posts by pasting, which included and tags that are overriding the CSS. It would be nice to be able to either tell WP to ignore those tags in posts or to strip them out of the db, but unfortunately they’re all different so your method won’t work. Any idea?

    THANKS!

  25. Posted July 11, 2008 at 3:25 pm | Permalink

    @ Mad Dog:

    The wp-includes/kses.php file in WordPress contains the commands to strip out codes from posts. You can add or subtract code within there to remove things manually, without the use of a Plugin. There may be Plugins that handle this through the Administration Panels, but I don’t know of one.

    UPDATE: Plugin authors heard my request for Plugins to change how kses.php strips content, adding and removing content stripping options. Plugins include Visual Code Editor WordPress Plugin and UPM HTML Tag Manager WordPress Plugin, among others.

  26. Posted July 15, 2008 at 12:45 pm | Permalink

    THANK YOU!!! I just upgraded my blog’s lightbox plugin from an older version to Lightbox2 and needed a way to update all the image code entries in my posts. I was going through each of them slowly and cursing myself for using so many images when I decided to search for someone way smarter about SQL databases than I am.

    I appreciate the time you took to create such a useful and explanatory post on the subject. You saved me a lot of tedious work!
    :-)

  27. Posted November 17, 2008 at 2:19 pm | Permalink

    You rock!! Thanks for saving me a ton of searching. My issue was left over [tag][/tag] tags from the Ulimate Tag Warrior after upgrading to 2.6.3.

    Thanks again!

  28. Posted December 3, 2008 at 7:46 am | Permalink

    It worked for me, yay !

    My sincere thanks for helping me solve an annoying problem, I am deeply grateful, Lorelle :)

  29. Posted January 13, 2009 at 7:43 am | Permalink

    I’ve been trying to clean up some badly coded pages. I need to replace/remove markup like fonts tags. I’ve tried to use wildcards with mysql:

    UPDATE wp_posts SET post_content = REPLACE (post_content,”,”);

    with no luck. Any advice would be much appreciated! Thanks for your time.

  30. Posted January 13, 2009 at 7:46 am | Permalink

    BTW, I’ve also tried:

    UPDATE wp_posts SET post_content = ” where post_content like ”

    No luck. Thanks

  31. Posted January 27, 2009 at 2:24 pm | Permalink

    Thank you so much for this post! I just moved my blog from one address to another, and I needed to update all of the internal linking on my site. It was done in a second, thanks to this guide! So thanks! :)

  32. Posted February 1, 2009 at 7:55 am | Permalink

    Thank you! Saved me hours of work! and saved 7 years of blogging! ;)

  33. NasMobile
    Posted February 21, 2009 at 3:23 am | Permalink

    Great post. I would add one check before No. 4. Run a select statement to see what fields you are changing before changing them. Something like:

    SELECT POST_CONTENT FROM WP_POSTS WHERE POST_CONTENT LIKE ‘Item to replace here’
    (NB: This might not be ‘pure’ MySQL statement – but I’m sure it would work in same was SQLServer).

    The ‘where’ clause must be the same as the clause in the update statement in No. 4.

    If this returns more values than you expect – DO NOT run No. 4. Fine tune, the select statement where clause e.g. ‘Item to replace here – this is better’.

    When you’re happy, adjust the No. 4 update statement:

    UPDATE wp_posts SET post_content = REPLACE (
    post_content,
    ‘Item to replace here – this is better’,
    ‘Replacement text here’);

    As you say Lorelle – do not touch this if you don’t know anything about MySQL.

    Goodluck. ( …… and ‘Just (don’t) do it’ ….. if you have any doubts – get the experts to take care of it for you!)

  34. Posted April 21, 2009 at 2:55 pm | Permalink

    VERY THANKS!

    WORKS FINE!

    you saved my life ;D

  35. George
    Posted June 29, 2009 at 4:00 am | Permalink

    Hi!

    I just made a big mistake! I had unistall wordpress from root!
    So:
    – I have database
    – I have all files in root

    How can I recover connection between database and public_html folder?

    Thanks!

  36. Posted June 29, 2009 at 2:20 pm | Permalink

    @George: This isn’t a database search and replace issue. Please check the , the online manual for WordPress Users, and for help on this issue.

  37. Posted July 14, 2009 at 11:23 am | Permalink

    Great tip, thanks very much! When I moved my wordpress stuff to a subdirectory, all my images broke because wp-content wasn’t in the right place any more.
    Cheers!

  38. steve
    Posted November 9, 2009 at 3:50 pm | Permalink

    thank you very much!, this saved me hours and hours of time!

  39. Posted November 19, 2009 at 11:12 pm | Permalink

    I’m moving my blog to a new hosting company. Since I’m moving the blog to the root from a subfolder /blog/, I need to change the wp_posts guid field on all the posts.
    Example: http://mymarketingvp.net/blog/18-revision-5/ [This is from the current database.]

    Following the steps above, I entered this SQL statement:
    update wp_posts set guid = replace(guid, ‘net/blog/’, ‘net/’)
    ["net" is the end of the domain name *.net]

    I got the following error message:
    Error
    SQL query:
    UPDATE wp_posts SET guid = replace( guid, ‘net / blog / ’, ‘net / ’ )
    MySQL said:
    #1054 – Unknown column ‘‘net’ in ‘field list’

    Notice how it added some spaces in the command. Same error when I remove the spaces.

    Any suggestions??? Thanks!

    • Posted November 21, 2009 at 8:08 am | Permalink

      As someone has just said, and I mentioned in the article, you have to be VERY specific. First, you put the spaces in, but the key is to use the EXACT name, not a word so easily mistaken as “net.” Change one thing at a time and backup backup backup backup.

  40. Posted November 20, 2009 at 9:50 pm | Permalink

    I solved my own problem when I did the exact same command…just entered the entire string not just “net”. So remember to enter the entire old and new domain names. It worked fine and changed it 159 times.

  41. Posted December 5, 2009 at 1:13 pm | Permalink

    Four years on, you may be interested to know about the newly released utility to make this easy – but do bear in mind that it’s designed to work across the entire database, and will cheerfully wreck your database.

    However, it’s very handy for migrations :-)

  42. Posted January 30, 2010 at 2:46 am | Permalink

    I’ve written a plugin at WordPress.org which enables you (with a GUI) to find and replace in your posts,pages and/or trashed items: http://wordpress.org/extend/plugins/find-replace/

  43. Posted February 3, 2010 at 6:53 am | Permalink

    I was going to do this with the Find and Replace plugin but now I’m not so sure… I might have to go in to 50 different places to change a url and image path.

  44. famous
    Posted April 23, 2010 at 7:02 pm | Permalink

    I tried using your info above, and if it worked it would be fantastic! What a time saver! Didn’t work though? why?

    UPDATE wp_options SET option_value = REPLACE (
    option_value,
    ‘email1@email.com’,
    ‘email2@email.com’);

    • Posted May 3, 2010 at 10:09 pm | Permalink

      It didn’t work because the names of the tables have been changed in WordPress. Did you check the table names in the latest version of WordPress?

  45. famous
    Posted April 23, 2010 at 7:07 pm | Permalink

    Sorry may have given the wrong impression. It appeared to work:
    0 row(s) affected. ( Query took 0.0169 sec )
    UPDATE wp_options SET option_value = REPLACE( option_value, ‘email1@email.com’,‘email2@email.com’);

    But didn’t replace anything???

  46. duytan
    Posted June 10, 2010 at 9:44 pm | Permalink

    hi, i would like replace an URL in database with structure bellow:
    httpv://youtube.com/watch=abcdef
    now i would like replace : to play video with my player.
    You can help me ?
    UPDATE wp_posts SET post_content = REPLACE (
    post_content,
    ‘httpv://youtube.com/watch=’,
    ‘);

    thanks !

  47. Posted June 22, 2010 at 1:25 pm | Permalink

    After stumbling ove DELETE clauses, I found this UPDATE post. Thanks!

    Anyone turn

    UPDATE wp_posts SET post_content = REPLACE (
    post_content,
    ”,
    ”);

    into a php cron job?

    Thanks,
    Mike

  48. Posted June 23, 2010 at 9:38 am | Permalink

    Sure Mike, what is your query exactly and why do you need this to be a cronjob?

  49. Rakesh
    Posted July 13, 2010 at 1:08 am | Permalink

    how can i replace single code with space

    • Posted July 13, 2010 at 8:01 am | Permalink

      The instructions are above. Just change what you want to search for with a space bar, though adding space is not a good thing, so I’d change the replaced item with nothing if that’s the goal. Just be careful, backup backup backup first, and ensure what you want to replace is very specific and unique.

  50. Paul Sylvester
    Posted September 21, 2010 at 7:45 am | Permalink

    I must say, that little tip has saved me so many times from havign to edit each and every article. I have bookmarked this page for future reference. Great job and great tip! Keep it up!!

  51. Arsham Mirshah
    Posted September 23, 2010 at 6:42 am | Permalink

    You ROCK Lorelle – thank you so much :)

  52. Posted November 11, 2010 at 9:34 pm | Permalink

    Hi Lorelle

    Thanks for this absolutely insightful and useful post. Exactly what I was looking for. However, I still have a slight problem. This discusses changing a constant (ie changing A to B). How would you change a variable, with constant factors? I presume in PC speak that would be a “wildcard”?

    To explain better – my problem is that I just restored a crashed blog, and now the directory structure is slightly different – instead of “url/blog” it’s simply on the root “url”. The posts appear fine, but all the images are still pointing to the old directory. How can I update all the URLS to REMOVE the directory so it changes from:

    url/blog/wp-content/uploads/year/month/image1.jpg
    url/blog/wp-content/uploads/year/month/image2.jpg
    url/blog/wp-content/uploads/year/month/image3.jpg
    etc.

    to

    url/wp-content/uploads/year/month/image1.jpg
    url/wp-content/uploads/year/month/image2.jpg
    url/wp-content/uploads/year/month/image3.jpg
    etc.

    Any help would be MUCH appreciated!

    Cheers

    Jeff

    • Posted November 17, 2010 at 1:09 pm | Permalink

      @Jeff: One at a time. There are no wildcards in search and replace in MySQL. I’ve tried, and while there is the LIKE command, it doesn’t really work in this example. You just have to do this one at a time, carefully.

  53. Posted November 17, 2010 at 7:40 pm | Permalink

    Thanks for the reply Lorelle. It’s a shame there is no wildcard option. Guess I’ll have to go about it the hard way!

  54. Seamus
    Posted November 19, 2010 at 4:20 pm | Permalink

    @jeff you can run a query to replace all. The example below this is presuming that you are updating content in posts.

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘url/blog/wp-content/uploads/’, ‘url/wp-content/uploads/’);

  55. Ganybhat
    Posted December 24, 2010 at 6:50 am | Permalink

    Worked very well.
    Thank you for the code.

  56. Hasan
    Posted January 7, 2011 at 6:26 pm | Permalink

    I realy apretiate your work.

    Thank you. It is very helpfull for me.

  57. Dale
    Posted January 31, 2011 at 9:39 pm | Permalink

    Thanks a bunch! This saved me and my associates many hours renaming media files uploaded to a WordPress site under GoDaddy’s previewdns.com. They don’t warn you that when the preview expires all the links to your images break!

  58. Millard
    Posted July 28, 2011 at 8:38 am | Permalink

    Why not just export your sql database, load it into a program like dreamweaver, do a find and replace, then do a “drop” all content in your database within phpmyadmin, and then just import your newly changed database.

    • Posted July 28, 2011 at 9:00 am | Permalink

      Um, let’s see, a search and replace within the database takes seconds. To do what you do…could be hours. Especially if you are working with a huge database of content. And think of the RISK! There’s a lot of opportunity for a glitch along the way.

      Honestly, what a bizarre thing to even suggest. Doing it in the database directly isn’t any better or faster than doing the same thing in a copy of the database in Dreamweaver. Bizarre suggestion.

  59. Posted September 1, 2011 at 5:54 am | Permalink

    Awesome post!!
    Tried that on one of my sites and it works!
    Just wondered what could be the problem that it didn’t replace 100%
    of the texts. in my case it ignored few posts from certain categories and also content
    in pages (not posts).. any idea?

    Thanks

    • Posted September 1, 2011 at 11:49 am | Permalink

      The only time search and replace doesn’t work in the database is when there is some characteristic that is different, as noted in the article. A space, capitalization, punctuation, spelling. Look for whatever is the minor difference. If you set the search and replace to change only posts, it would change only posts. If you set it to change in Pages, it would change Pages and not posts. Depends upon what you did.

      In the eight years of using search and replace in the MySQL database, the only thing that goes wrong is with the user – me. :D Pay close attention to details.

  60. Alps
    Posted September 15, 2011 at 4:47 am | Permalink

    this tool is my mainstay – what good would a db be without a search and replace … =) too bad there is no way for wild card characters to remove numbers position wise in posts that are numbered 1, 2, 3 etc …

  61. Usman
    Posted September 16, 2011 at 11:07 am | Permalink

    Nice Information, just what I need! :)

    There are hundreds of old link on my wp database, now how is it possible to do this?

    for example:
    I want to search:
    example.cc > Can I search and replace it like how you mentioned in your post?

    Thanks in Advance!

    • Posted September 16, 2011 at 11:51 am | Permalink

      Do it exactly as mentioned in the article, searching and replacing UNIQUE content and it will work. BACKUP first.

  62. Tony
    Posted October 27, 2011 at 7:19 am | Permalink

    Hey Lorelle. How are you? Would this work for and tags? We recently made a change and are not using any and anymore in posts, but we DO use them in other places in the blog formatting. We have 1,000 posts using and tags. I’d like to eliminate them all together in blog posts ONLY and no other places. Any ideas?

    • Posted October 27, 2011 at 1:30 pm | Permalink

      I don’t understand. You use “any” and “any more” (it isn’t a since word” in post content but also post formatting – like you have a style like div id="anymore"?

      Use the instructions I’ve given and be VERY specific with how you use it, with spaces and other details, and give it a try. BACKUP first.

  63. Posted April 23, 2012 at 9:56 am | Permalink

    Thanks for the excellent instructions. Before I start:
    The database is open and I clicked on the SQL tab. Then there’s a window and the first line is:
    SELECT * FROM `wp_posts` WHERE 1
    Should I delete, and then go with:
    UPDATE wp_posts SET post_content = REPLACE (
    post_content,
    ‘Item to replace here’,
    ‘Replacement text here’);

    Thanks!

  64. jacklynnpham
    Posted July 5, 2012 at 1:21 pm | Permalink

    Thanks sooo much! One of the most frightening things I’ve done in a while :). Worked great for all my posts, except it didn’t update the images on pages (or a least one page that I know of). How do I find the table with pages and not posts? Please feel free to email me directly!

  65. mcdonna
    Posted November 20, 2012 at 2:55 pm | Permalink

    Thanks for your clear and helpful article!

    Regarding backticks (comment #13), these are in fact MySQL standard for quoting identifiers. They are necessary only when the identifier conflicts with a reserved word. For example, if you name rows in your table “name” or “date.” Since WordPress does not use reserved words, but instead uses “post_name” or “post_title” and “post_date,” most WordPress programmers will never run into the problem.

  66. Posted April 8, 2013 at 3:00 pm | Permalink

    Thanks! Some things rarely change.

  67. Posted April 9, 2013 at 9:53 am | Permalink

    Thanks for the overview, having some trouble thought. I’m trying to search and replace a large chunk of content with a different large chunk of content. The content includes various spaces, padding, and shortcodes, and it isn’t able to find the content across my entire site. I know for a fact there are at least 50 pages with this content on it, but it’s only changing a few of the pages.

    Any thoughts? Is there a better way to do this for a large volume of content?

    Thanks in advance!

    • Posted April 9, 2013 at 12:27 pm | Permalink

      To search and replace, you MUST search for the EXACT content in order to find it. If it did not replace, it was not exact. Check it carefully. Editing 50 posts is a time waster but not a large volume to edit once you get the hang of it. Start by making sure that what you are searching for is exactly the same, character by character. Consider doing smaller chunks to make it easier. Thanks.

  68. Posted June 10, 2013 at 9:36 pm | Permalink

    cockknoker gay
    Somebody essentially assist to make seriously articles I might state.
    That is the very first time I frequented your web page and so far?
    I surprised with the analysis you made to create this actual submit extraordinary.
    Great activity!

  69. Posted June 11, 2013 at 2:41 am | Permalink

    Thanks in favor of sharing such a good thought, article is good,
    thats why i have read it completely

  70. Posted July 28, 2013 at 11:07 pm | Permalink

    Appreciating the hard work you put into your blog and in depth information you provide.
    It’s nice to come across a blog every once in a while that isn’t
    the same out of date rehashed information. Great read! I’ve saved your site and I’m
    including your RSS feeds to my Google account.


36 Trackbacks/Pingbacks

  1. [...] If there are many 404 Errors to a particular post, you can do a search and replace in your WordPress database, but be very careful. [...]

  2. [...] Att få exotiska tecken rätt var knepigare. Testade den här metoden för att konvertera ISO 8859-1 till UTF-8, men det funkade inget vidare. I slutändan valde jag att söka och ersätta i både inlägg och kommentarer. Tog lite tid, men det gick. Den som till skillnad från mig kan PHP/SQL skulle säkert kunna knåpa ihop ett skript. [...]

  3. [...] A “one question driven” purpose is the attempt to find a solution to one subject. For example, if you want to figure out how to how to search and replace in a MySQL database table, and the answer you get that helps you the most is the answer you need. [...]

  4. [...] Search and Replace in WordPress MySQL Database [...]

  5. [...] Upon trying to achieve XHTML 1.0 Strict, one of the thing i found out that i had been doing wrong all this while is that i put target=”_blank” HTML tag in most of my post link, this is not a valid XHTML for Strict and it won’t pass the validation, but i had been doing this since the very first day i blog! It will be troublesome if i’m going to search for every post and take away the target=”_blank”!! So i guess the only way to do this easily is by using the MySQL query, i went to Google around and found this very useful SQL query in Lorelle’s blog. [...]

  6. [...] Levar várias surras diárias da minha falta do php, do .htaccess, do próprio WordPress e até edição de MySQL “na unha”… [...]

  7. [...] seems that comments are sometimes not converted correctly, although everything else is. Normally, find and replace in MySQL databases is frowned upon, but because the gibberish is so unique (sorry to those who type entirely in gibberish), it is a [...]

  8. [...] Just do a search for the filename (ie. “smilie.jpg”) and edit the post to the new file location. You can do this right in WordPress admin area (under Manage) or use the blog’s search box. I didn’t have much post editing to do (makes me wonder what the heck I have those files uploaded for), but if your job is a big one you may prefer doing a database search and replace. [...]

  9. [...] the audio player code from <mp3> </mp3> to <audio: > using the Search and Replace in SQL database code as posted by Lorelle. I didn’t like the MP3-O-Matic player –with large files, it didn’t seem to [...]

  10. [...] http://lorelle.wordpress.com/2005/12/01/search-and-replace-in-wordpress-mysql-database/ [...]

  11. [...] Methode wurde z.B. von Lorelle schon beschrieben, basiert eigentlich auf einfachen MySQL-Befehlen ähnlich denen die WordPress [...]

  12. [...] very scary that you should do with great care (if at all)! Lorelle (WordPress guru) posted about search and replace in your WP database. I’ve used this before when I’ve decided to change some class names and didn’t want to go through [...]

  13. [...] ידעתי שעם קצת נבירה ברשת אמצא פתרון פשוט יחסית לעדכן את כל הקישורים באופן אוטומטי. אולי גם יש תוסף וורדפרס שעושה את העבודה. לבסוף חיפשתי ומצאתי את הפוסט הבא: “Search and Replace in WordpPress MySQL Database”. [...]

  14. [...] on WordPress came through again with her post about doing just what I needed. The solution was a simple UPDATE query on the wp_posts MySQL table. [...]

  15. [...]I recently migrated a blog from Blogger to WordPress and I think the following will help you [...]

  16. [...] are quicker ways of replacing links I’m told but I got scared and did it [...]

  17. [...] ידעתי שלאחר קצת נבירה ברשת (ככתוב google is your friend) אמצא פתרון פשוט יחסית לעדכן את כל הקישורים באופן אוטומטי ואולי גם אמצא תוסף וורדפרס שיעשה את העבודה. לבסוף מצאתי את הפוסט הבא: “Search and Replace in WordpPress MySQL Database”. [...]

  18. [...] found this great post at Lorelle’s blog about how to do terrifying scary dangerous stuff to your WordPress [...]

  19. [...] Handy tips for finding and replacing bits of text in all content: This is especially handy for updating links. But back up your database first. You may need it! lorelle.wordpress.com: Search and Replace in WordPress MySQL Database [...]

  20. [...] out how to fix those. This time, it was Lorelle Van Fossen who came to my rescue, with her “Search and Replace in WordPress MySQL Database” article from 2005. With Lorelle’s help, I was able to replace those lingering links in the [...]

  21. [...] matching the Movable Type importer format. It came with a ton of problems and I became the queen of search and replace in WordPress MySQL Database and search and replace across multiple files, a task no one should [...]

  22. [...] matching the Movable Type importer format. It came with a ton of problems and I became the queen of search and replace in WordPress MySQL Database and search and replace across multiple files, a task no one should [...]

  23. [...] Methode wurde z.B. von Lorelle schon beschrieben, basiert eigentlich auf einfachen MySQL-Befehlen ähnlich denen die WordPress [...]

  24. [...] The second thing you’ll need to do is replace all the instances of the previewdns.com URL in the database with the new, normal URL for your site (replacing http://www.yoursitedomain.com.previewdns.com withhttp://www.yoursitedomain.com)  Full, detailed instructions on how to do that can be found at Lorelle Van Fossen’s blog at http://lorelle.wordpress.com/2005/12/01/search-and-replace-in-wordpress-mysql-database/ [...]

  25. [...] Search and Replace in WordPress MySQL Database [...]

  26. [...] find and replace to remove all ref­er­ences to the old URL from the data­base. This can be done using a MySQL query (thanks Lorelle) or by using a GUI pro­vided by the Search and Replace [...]

  27. [...] via Search and Replace in WordPress MySQL Database « Lorelle on WordPress. [...]

  28. [...] http://lorelle.wordpress.com/2005/12/01/search-and-replace-in-wordpress-mysql-database/ [...]

  29. […] Sometimes we don’t just want to search our WordPress blogs. We want to search and replace. […]

  30. […] Search and Replace in WordPress MySQL Database […]

  31. […] Search and Replace in WordPress MySQL Database […]

  32. […] Search and Replace in WordPress MySQL Database […]

  33. […] Search and Replace in WordPress MySQL Database […]

  34. […] Methode wurde z.B. von Lorelle schon beschrieben, basiert eigentlich auf einfachen MySQL-Befehlen ähnlich denen die WordPress […]

  35. […] Single Post Pageview: This is the view of a single post on the site. It may look the same or different from the rest of the pageviews. Example: Search and Replace in WordPress MySQL Database […]

  36. […] Search and Replace in WordPress MySQL Database […]

Post a Comment

Follow

Get every new post delivered to your Inbox.

Join 20,689 other followers

%d bloggers like this: