How to replace string or add symbol in a database WordPress

Replace string or add symbol on all WordPress site

Sometimes as a result of rebranding company or just banal inattention there is a need to replace string or add/change some symbol in the word. For example, in my case it was replace “Company Name” to “Company Name®”. Well, traditionally here are some ways resolve this problem.

Problem: Replace string or add symbol on all WordPress site.

Solution:

1. This way uses replace in the database tables: post_content (content of the posts, include custom posts), meta_value (ACF), option_value (ACF):

update wp_posts set post_content = replace(post_content,'Company Name','Company Name®');
update wp_postmeta set meta_value = replace(meta_value,'Company Name','Company Name®');
update wp_options set option_value = replace(option_value,'Company Name','Company Name®');

2. This way uses Linux replace command. Be careful not to accidentally replace permalinks, slugs, etc.

sed -i 's/original/new/g' database.sql

,where:

sed - Stream EDitor
-i — in-place (i.e. save back to the original file)
The command string:

s — the substitute command
original — a regular expression describing the word to replace (or just the word itself)
new — the text to replace it with
g — global (i.e. replace all and not just the first occurrence)
database.sql — the file name

Note: If the string contain white spaces e.g. "Company Name" we should to double-quote the string and wrap it over once again with single quotes.

sed -i 's/'"Company Name"'/'"Company Name®"'/g' database.sql

UPD 15/09/21. Some time we need replace URL address of site, e.g. before import database from development server to live server, in this case we'll use single quote '"' instead of slash '/' in command.

sed -i 's"'https://wordpress.com/'"'https://helloadmin.com/'"g' database.sql

Sources:

1. https://askubuntu.com/questions/20414/find-and-replace-text-within-a-file-using-commands
2. https://stackoverflow.com/questions/47827749/replacing-text-with-sed-containing-whitespace-in-variable