Using "str_replace PHP" In MySQL With REPLACE Function

If you have large amounts of data in MySQL and want to change some values/characters in a particular field, it would be very inconvenient If you must be changed manually. There is MySQL function similar to str_replace in PHP that has a function to change the certain word with other words massively.

MYSQL REPLACE

MySQL REPLACE() replaces all the occurrences of a substring within a string. Perhaps we rarely use this function, but in certain situations, this function will be the savior of developers in data processing.

For example, a colleague of mine who has purchased the azoncastv2 theme. He had the data product with a very large number. Turns out he incorrectly entered the Amazon affiliate ID and asked me how to modify amazon affiliate ID in the table post massively?

By running the following query, we can change the string in the WordPress posts table massively

UPDATE wp_postmeta
SET meta_value = REPLACE(meta_value, 'your-old-tag-id', 'your-new-tag-id)
WHERE meta_value LIKE '%your-old-tag-id%'

UPDATE wp_postmeta

SET meta_value = REPLACE(meta_value, 'your-old-tag-id', 'your-new-tag-id)

WHERE meta_value LIKE '%your-old-tag-id%'

and this one

UPDATE wp_posts
SET post_content = REPLACE(post_content, 'your-old-tag-id', 'your-new-tag-id')
WHERE post_content LIKE '%your-old-tag-id%'

UPDATE wp_posts

SET post_content = REPLACE(post_content, 'your-old-tag-id', 'your-new-tag-id')

WHERE post_content LIKE '%your-old-tag-id%'

This simple query can be used to change a word in a set of strings rapidly. Thus my brief tutorial on the use REPLACE in MySQL to change a specific string in a set of strings.