How To Find And Replace an URL Or Text In A WordPress Database

Wanna know how to find and replace an URL or text in a WordPress database?

Yeah, we’ve been there.

In this article, we’ll show you how.

One of the main aspects of database management is knowing how to update text.

This is necessary if migrating from one domain to another or changing from non-SSL to SSL. Or the name of a product line changes and the website needs updating to reflect this.

For these situations, the ability to conduct a mass WordPress ’find and replace’ is vital. It saves a vast amount of time when compared to a manual update of all URLs and texts.

This article looks at several methods to find and replace URLs and texts in a WordPress database.

Reasons for Replacing URL or Text in a WordPress Database

There are many reasons to find and replace URLs and text in a WordPress database.  The most common reasons are moving to a new domain or adding an SSL certificate to a website.

At times, site owners may want to modify or change a specific text phrase such as the company name. Knowing how to perform a bulk find and replace can save lots of time.

It’s also possible to employ the same method on more than text. It can switch out URLs and images in your WordPress database. Mass find and replace capability proves useful when needing to:

• Update the Search Engine Optimization (SEO) keywords
• Correct spelling or grammar mistakes (by using a grammar and punctuation checker)
• Remove special characters or specific words
• Change and update URLs in the WordPress database when moving the website to a new host
• Fix broken links
• Update outdated content, shortcodes, or strings of text

Updating a website’s URL means more than changing the URL on the settings page. There are other things to remember and handle.

If the images on the website do not have an external CDN, the links to the previous URL are still embedded in the post. These links will display as broken when anyone visits the page.

In the case of small websites or blogs, it is workable to replace a couple of URLs manually. However, it is not feasible on large websites and blogs that have many images across the site.

The only practical way is to mass replace all the appropriate URLs in the database.

Note that having performed a bulk find and replace, there is no “undo” button. It is not possible to go back to the previous version.

The only way would be to conduct another massfind and replace process. So it is advisable to carry out a backup before a mass WordPress search and replace procedure.

The best method to find and replace content in a WordPress database is to run SQL queries. Input the required SQL queries into phpMyAdmin, a shell command line, or a PHP script.

For those who may prefer a plugin, there is a list of recommended plugins at the end of the article.

How to Write a MySQL Query to Conduct Find and Replace On a WordPress Database

Maybe you don’t want to install any more plugins but you do know a little bit about coding. In this case, it’s possible to use phpMyAdmin to conduct a find and replace in the WordPress database.

First though a little guidance on how to find your WordPress database name. This is necessary for the next step.

Click on the public_html folder (or www, htdocs, or httpdocs, depending on the hosting provider). This is usually found in the navigation menu on the left-hand side of the screen.

Find the wp-config.php file and double-click to open it.  The name of your database is in the line:

define(‘DB_NAME’, ‘Database Name‘);

Note it down and go back to the dashboard. In the Databases section, click on the phpMyAdmin icon.

Find the WordPress database and click on the Enter phpMyAdmin button. Then, on the top menu bar, click on the SQL tab.

Insert the following line of code into the text box:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'Text to search, 'Text to replace it with');

Here’s a brief explanation of what each of the placeholder texts means. This will help you to replace them with the correct information.

TABLE_NAME stands for the name of the table to alter.

Find the names of all tables in the Structure tab in the phpMyAdmin database. These include wp_comments, wp_posts, and wp_users.

FIELD_NAME stands for the name of the field where the targeted data is. All the field names are displayed in the table you wish to modify.

‘Text to search’ stands for the word or phrase that requires editing.

‘Text to replace it with’ stands for the word or phrase that will replace the original text.

For example:

A food blogger notices that they have misspelled the word “raspberry” in all their posts. To correct this mistake and replace all occurrences of “rasberry” with “raspberry”, the MySQL query would look like this:

update wp_posts set post_content = replace(post_content, 'rasberry, 'raspberry');

Having ensured that the query is written correctly, click on the Go button to make the required change.

Running a Database Query in the Command Line

Running a database query directly in the command line is possible if you have shell access to the webserver.

If you are using MySQL, sign in using your WordPress database credentials.

mysql -u root -p database_name

At the mysql> prompt, enter the desired SQL queries and press ENTER.

mysql> UPDATE wp_options SET option_value = replace(option_value, 'old_domain.com', 'new_domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://domain.co', 'https://domain.co');

Query OK, 0 rows affected (0.08 sec)

Rows matched: 354 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://domain.co', 'https://domain.co');

Query OK, 0 rows affected (0.01 sec)

Rows matched: 22 Changed: 0 Warnings: 0

mysql> UPDATE wp_comments SET comment_content = replace(comment_content , 'http://domain.co', 'https://domain.co');

Query OK, 0 rows affected (0.01 sec)

Rows matched: 10 Changed: 0 Warnings: 0

Once finished, exit MySQL.

exit

Writing a Query Using PHP Script

If you don’t have phpMyAdmin or shell access, another option is to use PHP script.

After entering your database credentials, insert the query into the $sql variable.

<?php

$mysqli = db_connect('localhost','database_username','database_password','database_name'); 

$sql = "UPDATE wp_posts SET post_content = replace(post_content, 'http://domain.co', 'https://domain.co'"; 

function db_connect($host,$user,$pass,$db) {

 $mysqli = new mysqli($host, $user, $pass, $db);

 $mysqli->set_charset("utf8");

 if($mysqli->connect_error) 

 die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

 return $mysqli;

}

if ($mysqli->query($sql) === TRUE) {

    echo "Record updated successfully";

} else {

    echo "Error: " . $sql . "

" . $mysqli->error;

}

$mysqli->close();

Remember that if executed incorrectly, this script could break your WordPress website. If in doubt, double-check with a developer or the web host.

Search and Replace Using a Plugin

Using a plugin is the best option for those without coding experience.

The following tested plugins are reliable and received high ratings from users.

Better Search Replace

This plugin will update URLs across all or several selected tables in a database. Users can execute search and replace commands from inside their WordPress admin area.

WP Migrate DB

This plugin is solid and reliable for searching and replacing data inside serialized data. Users install the plugin on the original site.

Conducting a search and replace on a URL string and web root generates a new database dump. This information is then exported.

Users import this into the new URL hosted database.

Search & Replace

This is a free plugin developed by Inpsyde. Users can replace URLs, words, and phrases in their WordPress database straight from the dashboard.

What makes this plugin special is the ability to create a database backup by clicking one button. To do this, go to the Backup Database tab and click on the Create SQL File button.

Velvet Blues Update URLs

This plugin enables the modification of URLs in posts, pages, excerpts, and more. Users insert the old and new URLs and choose the places in which to update them.

Change the plugin settings in Tools » Update URLs page.

Ending thoughts on how to find and replace URL in WordPress database

Knowing how to conduct a bulk ‘find and replace’ can be invaluable. It is useful for updating keywords or altering a specific word or piece of text across all posts.

There are several techniques available to accomplish this. The four recommended methods mentioned in this article are:

• Write a MySQL Query
• Run a query in the command line
• Write a query in a PHP script
• Use a plugin

If you enjoyed reading this article on how to find and replace url in WordPress database, you should check out this one about how to do a WordPress database reset.

We also wrote about a few related subjects like WordPress database schema, how to do a WordPress database cleanup and how to scan WordPress database for malware.