Posts Tagged ‘Joomla’

Find and Replace Text in MySQL Database using SQL REPLACE

No Comments »

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');

update jos_docman set dmurl = replace(dmurl, 'click.net', 'm4u.com.pk')

The above statement will replace all instances of ‘click.net’ to ‘m4u.com.pk’ in the field of dmurl of jos_docman table.

Enjoy!