A typical case of replacing a part of the string to another substring in a database table is switching between site domains or URLs, such as when you want to transfer your WordPress blog from one domain to another. You have to change all values containing the original site URL to the new one in the wp_options and wp_posts table.
For example, when you transfer from olddomain.com to newdomain.com, you will better perform the following SQL query in the wordpress table wp_posts:
UPDATE wp_posts SET guid = REPLACE(guid, 'olddomain.com', 'newdomain.com')
And the MySQL function REPLACE() makes sure all substrings ‘olddomain.com’ in the value of the guid field will be replaced by ‘newdomain.com’.