When it comes to text manipulation, SQL is lacking.  It offers neither the flexibility of other programming languages (PHP, Java, etc), nor the user-friendliness of a word processor.  Nevertheless, with a little string manipulation creativity, combined with a couple of lesser-know SQL functions, it is not only possible, but relatively easy to accomplish.

The two functions that make search and replace possible are:

locate('search-string',field)
substring(field, p1, p2)

locate() returns the 1-based index of the 'search-string' inside 'field'.
substring() delivers what is promises by returning a substring of 'field' starting at p1 and ending at p2.

We will use the following test table to test our procedure:

CREATE TABLE replace_test(
id int(11) auto_increment primary key,
the_text varchar(1000));

INSERT INTO replace_test(the_text)
VALUES('Test text Test text Test text Test text');

Our example will search for the string 'text' and replace it with 'replacement'

UPDATE replace_test
SET the_text =
CONCAT(
SUBSTRING(the_text,1,LOCATE('text',the_text)-1),
"replacement",
SUBSTRING(the_text,LOCATE('text',the_text)+LENGTH('text'))
)
WHERE {your conditions};

1) Create substring between beginning of field and starting position of 'text'
2) Add 'replacement' to the substring
3) Create and add a substring between the last position of 'text' and the end of the field.

This will effectively replace the first instance of 'text', with 'replacement'. To perform the query on all fields in the table, omit the WHERE clause.

This works great if we are only replacing one instance of the searched string per field. If we want to replace several instances per field, we can repeat the query until all instances have been replaced. This will require a stored procedure.

CREATE PROCEDURE search_and_replace(
IN rowId INT(11),
IN searchString varchar(100),
IN stringReplace varchar(100)
)
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num > 0 DO
SELECT LOCATE(searchString,the_text) INTO num FROM
replace_test WHERE id = rowId LIMIT 1;
IF num > 0 THEN
UPDATE replace_test SET the_text = CONCAT(
SUBSTRING(the_text,1,LOCATE(searchString,the_text)-1),
stringReplace,
SUBSTRING(the_text,LOCATE(searchString,the_text)+LENGTH(searchString))) WHERE id = rowId;
END IF;
END WHILE;
END;//
 CALL search_and_replace('text','replacement'rowID);

This procedure takes the search and replacement strings as parameters. It repeats the search and replace from above, until no instance of the search string can be found in the field, specified by the third parameter, the id of the row.

Make sure to switch your end of line delimiter to something other than a semicolon so that the semicolons in the procedure do not conflict with MySQL.

DELIMITER //

If you make a mistake, the stored procedure can be removed with:

DROP PROCEDURE search_and_replace

To view all the procedures you have stored use

SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbname';

You might also be interested in