MySQL Search and Replace

Structured Query Language (SQL) is the computer programming language used to create, add to, modify and query databases. Occasionally, it's necessary to change all instances of a field within a database. Use the SQL search and replace function to perform this task.

Replace Function

SQL has a built-in Replace() function that allows all instances of specific text to be replaced with another piece of text. The syntax of the Replace() function follows.

Replace(FIELD, old_string, new_string);

Update Example

The Replace() function can work with the Update function to modify the contents of all fields within a specific table. See the example below.

update TABLE set FIELD = replace(FIELD, 'old string', 'new string');

Select Example

Using the Replace() function with a select statement allows you to modify the results of a query for display to a user without changing the fields within the database. See the example below.

Select Replace('string to be modified', 'old_string', 'new_string');

References

• MySQL: String Functions