Using REPLACE in an UPDATE statement

Date Posted: June 24, 2015, 6:06 am


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

About author


Carl Victor Fontanos

A fine gentleman specializing in front-end and back-end development with extensive experience building high performance web applications that keeps users engaged and help businesses grow.

 
Hire Me