Find and replace a row in MySQL using data from another table

Question

I have two MySQL tables, and I want to find and replace text strings with one, using data in the other.

texts table:

 +---------------------+ | messages | +---------------------+ | 'thx guys' | | 'i think ur great' | | 'thx again' | | ' u rock' | +---------------------+ 

dictionary table:

 +--------------+---------------+ | bad_spelling | good_spelling | +--------------+---------------+ | 'thx' | 'thanks' | | ' u ' | ' you ' | | ' r ' | ' are ' | +--------------+---------------+ 

I want SQL to go through and look at every line in the messages and replace each instance of bad_spelling with good_spelling and do this for all the bad_spelling and good_spelling pairs.

The closest I got this:

 update texts, dictionary set texts.message = replace(texts.message, dictionary.bad_spelling, dictionary.good_spelling) 

But this only changes “thanks” to “thanks” (in two lines) and does not replace “u” with “you” or “r” with “are.”

Any ideas how to make it use all the lines in the dictionary in the replace statement?

---

PS forgot to mention that this is a small example, and in a real situation I will have many pairs to find / replace, which can be added over time.

Answer 1

I have never used MySql, so this is just a theory based on my other work with databases. When you read other answers while trying to use REPLACE (), I thought I could post this and get someone with MySql syntax to try a few ideas to make a basic solution.

here is the SQL Server code that does most of the work for you:

 DECLARE @Source table (Texts varchar(50)) INSERT @Source VALUES ('thx guys') INSERT @Source VALUES ('i think ur great') INSERT @Source VALUES ('thx again') INSERT @Source VALUES ('u rock') DECLARE @Dictionary table (bad_spelling varchar(50), good_spelling varchar(50)) INSERT @Dictionary VALUES ('thx', 'thanks') INSERT @Dictionary VALUES ('u', 'you') INSERT @Dictionary VALUES ('r', 'are') SELECT t.Texts,COALESCE(d.good_spelling,c.ListValue) AS WordToUse FROM @Source t CROSS APPLY dbo.FN_ListToTable(' ',t.Texts) c LEFT OUTER JOIN @Dictionary d ON c.ListValue=d.bad_spelling 

OUTPUT:

 Texts WordToUse ------------------ --------- thx guys thanks thx guys guys i think ur great i i think ur great think i think ur great you i think ur great are i think ur great great thx again thanks thx again again u rock you u rock rock (11 row(s) affected) 

It is better to use a "real" PC than the actual "Texts" in the query above, but the OP does not list many columns in this table, so I use "Texts".

Using SQL Server, you need to use some funky XML syntax to concatenate lines backwards (so I won’t show this code since it doesn’t matter), but using MySql GROUP_CONCAT () you should be able to concatenate word strings back into phrase strings .

the code for the split function (SQL Server) and its actions can be found here: SQL Server: split operations

Answer 2

You need to repeatedly name the text Replace:

 Update ... Set texts.message = Replace( Replace( Replace( texts.message, 'thx ', 'thanks ' ) , ' u ', ' you ') , ' r ', ' are ') 

EDIT Given that you said that you have many replacements, you will need to do this in a cursor with several calls to UPDATE statements. Something like (I have not tested this at all, so be careful):

 Create Temporary Table ReplaceValues ( BeforeText varchar(100) not null , AfterText varchar(100) not null ) Insert ReplaceValues(BeforeText, AfterText) Values('thx ', 'thanks ') Insert ReplaceValues(BeforeText, AfterText) Values(' u ', ' you ') Insert ReplaceValues(BeforeText, AfterText) Values(' r ', ' are ') DECLARE done int DEFAULT(0) DECLARE BeforeValue varchar(100); DECLARE AfterValue varchar(100); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DECLARE ReplaceList CURSOR FOR Select BeforeText, AfterText From ReplaceValues; OPEN ReplaceList; REPEAT If NOT done THEN FETCH ReplaceList INTO BeforeValue, AfterValue; Update texts Set texts.message = REPLACE(texts.message, BeforeValue, AfterValue); END IF UNTIL done END REPEAT; CLOSE ReplaceList; 

You can translate all this into a procedure so that you can call it later.

Answer 3

This does not go away completely, because although the replacement was performed x times (where x is the number of lines in the dictionary), there is only one update (last).

Transactions do not record intermediate results and therefore cannot see them as input values ​​for the next batch of substitutions.

Since (AFAIK) MySQL does not support recursive queries, you will have to resort to a procedural approach.

Answer 4

You need to execute the request many times. Since this is a clean-up type operation that you usually do sometimes, I suggest you execute the following query until something is updated. I don't know how to do this with MySql , but in SQL Server it would check the number of rows updated (which is the result of executing this UPDATE query) and run UPDATE again until no rows are updated.

 update texts, dictionary set texts.message = replace(texts.message, dictionary.bad_spelling, dictionary.good_spelling) where texts.message <> replace(texts.message, dictionary.bad_spelling, dictionary.good_spelling)