Find and Replace a String Across the Entire WordPress Database

Home » Coding » Find and Replace a String Across the Entire WordPress Database
December 15, 2019 Coding, Linux, MySQL
Find and Replace a String Across the Entire WordPress Database

Rebranding, typo corrections, or URL migrations often require finding and replacing a string across the entire WordPress database. Depending on what you have access to, there are two main approaches: direct SQL queries for targeted column updates, or sed on a SQL dump file for a full-file search-and-replace before import.

Problem: A domain name, URL, or piece of text needs to be updated across an entire WordPress database — but a simple SQL REPLACE() call would corrupt serialised PHP values stored in options and post meta.

Solution: Use WP-CLI's wp search-replace 'old' 'new' --all-tables command, which handles serialised data correctly and supports a --dry-run to preview changes first. For server-only access without WP-CLI, the Search Replace DB PHP script handles serialisation safely.

Method 1 — SQL queries (via phpMyAdmin or command line). This targets only the columns that typically hold user-facing content:

-- Post content (posts, CPT content)
UPDATE wp_posts     SET post_content  = REPLACE(post_content,  'Company Name', 'Company Name®');

-- Custom field values (ACF, post meta)
UPDATE wp_postmeta  SET meta_value    = REPLACE(meta_value,    'Company Name', 'Company Name®');

-- Options (ACF option pages, theme settings)
UPDATE wp_options   SET option_value  = REPLACE(option_value,  'Company Name', 'Company Name®');

Method 2 — sed on a SQL dump file (Linux/macOS). Use this when you need to replace strings in a full database export before importing it to a new server:

# Simple replacement (no spaces in strings)
sed -i 's/Company/Corporation/g' database.sql

# String with spaces — wrap in single-quote groups
sed -i 's/'"Company Name"'/'"Company Name®"'/g' database.sql

When migrating a site between domains, use a different delimiter to avoid conflicts with forward slashes in URLs. The delimiter can be any character that does not appear in the strings being replaced — a double quote works well:

sed -i 's"https://old-domain.com/"https://new-domain.com/"g' database.sql

If your site uses serialised PHP data (common in ACF or widget settings), a plain string replacement will corrupt the serialised length counters. In that case, use the dedicated Better Search Replace plugin or the WP-CLI command instead:

wp search-replace 'https://old-domain.com' 'https://new-domain.com' --all-tables

NOTE: Always back up the database before any bulk replacement. The SQL REPLACE() function and sed -i both modify data in place with no built-in undo. Test with a SELECT query or a copy of the dump first to confirm the replacement scope matches your expectation.

Related posts