0

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]    |

1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.