Using REPLACE in an UPDATE statement

REPLACE takes in three strings as arguments and returns a string as a result.

1
REPLACE(item1, item2, item3)

item1 is our original string. item2 is the subtring we want replaced. item2 is the string that will replace item2 is the resultant string. This resultant string is represented by the REPLACE function itself. The following example shows how the REPLACE function works.

Let’s say you have thousands of posts that contain your site’s link, ex: http://example.com and you want to change it to a sub-domain link, ex: http://sub.example.com, the SQL command bellow will do exactly what we want to achieve:

1
UPDATE posts_table SET content = replace(content, 'http://example.com', 'http://sub.example.com') WHERE content LIKE '%http://sub.example.com%';

posts_table – Name of the table that contains all your posts
content – Column name that contains the contents of your posts