I don’t know about you but there have been so many times where I needed to replace one string of text in all tables in my WordPress database for another, usually a domain name or the like. It can be quite an annoyingly large amount of effort, but not any more!

I’m going to show you two super easy ways to do this using phpMyAdmin or just your basic Notepad or TextEdit. The great thing about these two methods is that you won’t need to install any plugins or additional files. They are super simple and easy to use.

Using SQL and phpMyAdmin

Whenever I build a new WordPress site, I usually start off with a staging area where I can mess around, tweak code and get all the bugs ironed out before I launch the live site. The problem with doing this though, is that when it comes time to launch, all the URLs of the site are wrong. Naturally, the obvious thing to do is to set the WordPress Address and Site Address to the correct new URL and all should be fine, but oops, some of my links are hardcoded in the database (this happens for many reasons, including poorly built plugins) and now I have to manually change each one via phpMyAdmin. Sounding familiar?

This is an extremely common issue that WordPress users face and one that can be quite frustrating at times. Ok, so here’s what you have to do.

Step One

Login to phpMyAdmin and select the database that you want to work in (if you have more than one). Then click on SQL.

Step Two

Before you proceed, make sure you’ve backed up your database for safe keeping.

Copy the below SQL Statements, which define a global_replace procedure which you will be able to use in the future, whenever you need it, and paste them into the “Run SQL Query” box. Click Go.

# must change statement delimiter if we are using interactive shell
DELIMITER ;;
CREATE PROCEDURE `global_replace` (IN `from_string` varchar(255), IN `to_string` varchar(255))
BEGIN
    # declare variable
    DECLARE db_name, statement_string VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    
    # declare cursor to receive subquery results. The subquery is
    # generating the UPDATE statements that will be executed next
    # for the actual string replacement operation to take place. 
    # We make use of the meta database `information_schema` to 
    # find all our needed table columns
    DECLARE cur CURSOR FOR (SELECT CONCAT('UPDATE `', table_name, '` SET `', column_name, '` = replace(`', column_name,'`, \'', from_string, '\', \'', to_string, '\');')
    FROM information_schema.columns
    WHERE table_schema = DATABASE());
    
    # declare handle 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    # open cursor
    OPEN cur;
    
    # starts the loop
    the_loop: LOOP
        # get the values of each column into our variables
        FETCH cur INTO statement_string;
        IF done THEN
          LEAVE the_loop;
        END IF;

        # carry out main loop logic

        # we must store this local variable to a user defined 
        # variable, because the PREPARE only accepts user 
        # defined variable
        SET @tmp = statement_string;
        PREPARE statement FROM @tmp;
        EXECUTE statement;
        DEALLOCATE PREPARE statement;
    END LOOP the_loop;
     
    CLOSE cur;
END;;
DELIMITER ;

The above code creates a really nice little procedure which we can reuse whenever we need to do a find and replace across all the tables in our database. Very handy when it comes to WordPress websites.

Step Three

Finally, copy the following code, replacing the old and new URLs (or any other text) with your own URLs (or any other string or text), and paste it into the SQL query box again. Click Go, and that’s that. All the old URLs will be replaced with the new ones.

CALL global_replace('http://www.oldurl.com', 'http://www.newurl.com');

If you don’t think you’re going to use the procedure again (created in Step Two), simply run the following final snippet of code. However,  I’d highly recommend you leave the procedure loaded and ready to use next time. Most often there isn’t a need to run this final code snippet, because we plan on reusing our little global procedure.

DROP PROCEDURE `global_replace`;

Credit: This method comes from an excellent post here.

Alternative Method: Using Find and Replace

So the first method above is my favourite and the one I would recommend using because it’s a simple code execution (really simple once you’ve loaded that handy procedure). However, there are those who are not comfortable with running a SQL query on their live database…so here’s a nice easy method to replace all your old links.

Step One

Login to phpMyAdmin and Export your database.

Step Two

Once downloaded to your local machine, open your database with your favourite text editor  (Notepad for Windows users or TextEdit for Mac users would be fine). Save a copy of the database with a new name and then run a find and replace search for your URLs.

Step Three

Save the new SQL file with your updated links.

Step Four

Drop all tables on your database and Import your newly created SQL file and you’re done. That’s it.

Wrapping it up

So there you have it. Two very different but equally simple techniques for updating any and all tables inside your WordPress databases. These two methods are a life saver when it comes to launching a new site or moving to a new URL. If you’re not up for doing all of this yourself, then feel free to get in touch with us and we’d be happy to give you a hand with your website.

If you have any questions or would like to add anything to these methods, feel free to drop us a comment below.