MySQL – REPLACE Multiple Matches in One Query

join;MySQLreplace

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

Best Answer

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.

Related Solutions

Mysql – Query to find and replace text in all tables and fields of a thesql db

You need to use the information_schema database to generate the script.

Collect all columns from every table of every database that have the following criteria:

Exclude the following databases:
- information_schema
- performance_schema
- mysql
COLUMN_TYPE values with one of the following characteristics:
- starts with CHAR(
- starts with VARCHAR(
- ends with TEXT (TEXT, MEDIUMTEXT, LONGTEXT)

Here is the query to get those columns

SELECT table_schema,table_name,column_name FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text');

Using the above query, construct a set of queries that outputs SQL for converting fuschia to fuchsia:

SELECT CONCAT('UPDATE ',table_schema,'.',table_name,
' SET ',column_name,'=REPLACE(',column_name,',''fuschia'',''fuchsia'');')
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%'
OR column_type LIKE 'varchar(%'
OR column_type LIKE '%text');

Take that query and send its output to a text file. Import text into mysql:

SQL="SELECT CONCAT('UPDATE ',table_schema,'.',table_name,"
SQL="${SQL}' SET \`',column_name,'\`=REPLACE(\`',column_name,'\`,''fuschia'',''fuchsia'');') "
SQL="${SQL} FROM information_schema.columns WHERE "
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema') "
SQL="${SQL} AND (column_type LIKE 'char(%' "
SQL="${SQL} OR column_type LIKE 'varchar(%'"
SQL="${SQL} OR column_type LIKE '%text');"
mysql -u... -p... -ANe"${SQL}" > GlobalReplace_fuschia_to_fuchsia.sql
less GlobalReplace_fuschia_to_fuchsia.sql

If the file looks good, feel free to execute it.

Give it a Try !!!

Mysql – UPDATE SET REPLACE() matches but does not change

Look carefully at the query's output

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The expression Rows matched is based on WHERE bug_id=452;. That matched.

When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment means.'

To verify that run this:

SELECT REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') replace_result
FROM bugs_fulltext WHERE bug_id=452;

If you getback the old value, then the old value was not in the row to begin with.

You should just directly update it (overwrite it):

UPDATE bugs_fulltext
SET short_desc = 'I don\'t know what my previous comment means.'
WHERE bug_id=452;