REPLACE(orig,match,replacement) multiple matches in one query I'm trying to categorize Magic The Gathering cards. Here is a simplified version of what I'm doing/trying.

Is it reasonable/doable to get my desired output from mysql?  If so, how?

Or am I going to need to use some php? (which I can figure out pretty easily) I'd rather do it through a query if i can.



I run:

SELECT REPLACE(orig.text, matches.text, matches.replacement) 
    FROM orig 
JOIN matches 
    ON orig.text LIKE CONCAT('%',matches.text,'%') ;




I want:

| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]  


In the actual app, there would be another column, like the card name.



I actually get:

| REPLACE(orig.text,matches.text,matches.replacement) |
+-----------------------------------------------------+
| [who] run really fast                               |
| [who] run really slow                               |
| [who] shop really fast                              |
| [who] shop really qu[who]ckly                       |
| [who] shop really slowly                            |
| i [action] really fast                              |
| i [action] really slow                              |
| i run really [speed]                                |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i [action] really fast                              |
| i [action] really quickly                           |
| i [action] really slowly                            |





orig table

| i run really fast     |
| i run really slow     |
| i shop really fast    |
| i shop really quickly |
| i shop really slowly 


matches table

| text    | replacement |
+---------+-------------+
| i       | [who]       |
| run     | [action]    |
| fast    | [speed]     |
| slowly  | [speed]     |
| quickly | [speed]     |
| shop    | [action]    |
A stored function did the trick. I spent a lot of time playing with RECURSIVE CTE only to find that stored functions have loops. Twas fun. 

Anyway. I also have a table 'replaced' with columns 'text' (the original) and 'tokenized' (the updated).

So I run:
UPDATE replaced SET tokenized = tokenize_text(text);

and i have stored function:


DELIMITER $$
DROP FUNCTION IF EXISTS tokenize_text $$
CREATE FUNCTION tokenize_text(the_text varchar(1000)) RETURNS VARCHAR(1000) 
    READS SQL DATA
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_match varchar(1000) DEFAULT "";
 DECLARE v_replacement varchar(1000) DEFAULT "";
 DECLARE v_output varchar(1000) DEFAULT CONCAT(' ',the_text, ' ');

 -- declare cursor for matches
 DEClARE matches_cursor CURSOR FOR 
    SELECT text, replacement FROM matches;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = TRUE;

 OPEN matches_cursor;

 change_text: LOOP

 FETCH matches_cursor INTO v_match, v_replacement;

 IF v_finished THEN 
 LEAVE change_text;
 END IF;

 -- build email list
 SET v_output = REPLACE(v_output, CONCAT(' ',v_match,' '), CONCAT(' ',v_replacement,' '));

 END LOOP change_text;

 CLOSE matches_cursor;
 RETURN v_output;
END$$

DELIMITER ;


To test, I do select * FROM replaced'; and get:

+-----------------------+---------------------------------+
| text                  | tokenized                       |
+-----------------------+---------------------------------+
| i run really fast     |  [who] [action] really [speed]  |
| i run really slow     |  [who] [action] really slow     |
| i shop really fast    |  [who] [action] really [speed]  |
| i shop really quickly |  [who] [action] really [speed]  |
| i shop really slowly  |  [who] [action] really [speed]  |
+-----------------------+---------------------------------+


Which is correct. It turns out I didn't have a match for 'slow', so it doesn't get replaced with [speed] like the other speed-specific adjectives.