The REPLACE() function in MySQL works quite well but I recently came across a situation where I wanted only replace matching whole words. Not only that but I also wanted to conduct a table based dictionary search and replace for all matching words. To answer my needs, I wrote a simple MySQL Function. The idea is to pass a phrase to the function and the function in turn would search and replace all occurrences of target words from the dictionary with new words.
First build and populate the dictionary table:
CREATE TABLE YOURDATABASE.dictionary (
`wordID` int(11) NOT NULL AUTO_INCREMENT
,`strBeforeWord` varchar(45) NOT NULL
,`strAfterWord` varchar(45) NOT NULL
PRIMARY KEY (`wordID`)
) ENGINE=MyISAM;
INSERT INTO YOURDATABASE.dictionary(strBeforeWord,strAfterWord)
VALUES ('quick','slow')
,('brown','green')
,('fox','turtle')
,('jumped','climbed')
,('over','under');
Now we build the function.
DELIMITER $$
CREATE FUNCTION YOURDATABASE.ReplaceWords(OrigValue VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE done int DEFAULT(0);
DECLARE BeforeValue varchar(255);
DECLARE AfterValue varchar(255);
DECLARE ReturnValue varchar(255);
DECLARE ReplaceList CURSOR FOR SELECT strBeforeWord, strAfterWord FROM YOURDATABASE.dictionary;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN ReplaceList;
SET ReturnValue = OrigValue;
REPEAT
IF NOT done THEN
FETCH ReplaceList INTO BeforeValue, AfterValue;
SET ReturnValue = REPLACE(REPLACE(ReturnValue,' ','|'), CONCAT('|',TRIM(BeforeValue),'|'), CONCAT(' ',AfterValue,' '));
END IF;
UNTIL done
END REPEAT;
CLOSE ReplaceList;
SET ReturnValue = TRIM(REPLACE(ReturnValue,'|',' '));
RETURN ReturnValue;
END$$
There you have it. To call this function simply run something like this:
SELECT YOURDATABASE.ReplaceWords('The quick brown fox jumped over the fence');
The resulting replaced statement will look like this:
The slow green turtle climbed under the fence
There are a few things that I might do to make this a bit more efficient such as add some sort of WHERE statement to the ReplaceList cursor to cut down the rows required to loop. I might also come up with a more complicated word delimiter, currently the pipe ‘|’ in the above example.
Enjoy!