SQL Replace String

Created February 16, 2011

Have you ever wanted to replace a particular part of a string in SQL and you don't want to go through the hassle of coding the query and using PHP functions to parse and replace that specific part of the string. Well, now you don't have to... There is a simple solution where you can replace and return the remainder of a string in SQL. The query is as follows:

UPDATE table_name
SET url_string=REPLACE(url_string, 'http://www.base_url.com/', '')

In the above query 'table_name' is the name of our table and 'url_string' is the string that we want to have a portion of it replaced. In this case we have just set any row that has 'http://www.base_url.com/' to return a blank space '' in place of that string.

So say if your 'url_string' contained the value http://www.base_url.com/uploads/image1.jpg. The above query would change that value to uploads/image1.jpg

There you go... Easy string replace in SQL. Hope this helps someone ;)