Mysql String Replace



MySQL has a built-in function replace() function to perform string replace similar to PHP str_replace through mysql query.

Mysql replace() function is used to replace strings in the text in the fields of the mysql table.

Mysql String Replace() function Syntax

replace(newstring, search_string, replace_text)

The REPLACE (newstring, search_string, replace_text) function returns new string newstring by searching and replacing all the occurrence of the substrubg search_string with replace_text. This function does a case-sensitive search.

As an example, look at the following mysql query:

 
mysql> SELECT REPLACE ('mysqlstringfunction', 'i', 'I') from mytable;

This would give output:

mysqlstrIngfunctIon

Here, in this output small "i" is replaced with capital "I".

Mysql Replace() Usage Example

As an example during Drupal migration, we have to sometimes make sure the content added on one environment is updated when moved to the new envirnment (example staging to production). This scenario typically happens in the 'body' column of table 'node_revisions' when the links are hard-coded to reflect staging environment. These needs to be updated when migration to production is done.

Following is the Mysql query that uses replace() function to do string replace:

update node_revisions set body = replace(body, 'oldurl', 'newurl');