mysql调用其他表数据,使用另一个表中的数据在MySQL中查找和替换字符串

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

Table texts:

+---------------------+

| messages |

+---------------------+

| 'thx guys' |

| 'i think u r great' |

| 'thx again' |

| ' u rock' |

+---------------------+

Table dictionary:

+--------------+---------------+

| bad_spelling | good_spelling |

+--------------+---------------+

| 'thx' | 'thanks' |

| ' u ' | ' you ' |

| ' r ' | ' are ' |

+--------------+---------------+

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

The closest I have gotten is this:

update texts, dictionary

set texts.message = replace(texts.message,

dictionary.bad_spelling,

dictionary.good_spelling)

But this only changes "thx" to "thanks" (in two rows) and does not go on to replace " u " with " you" or " r " with " are ."

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

PS forgot to mention that this is a small example and in the real thing I will have a lot of find/replace pairs, which may get added to over time.

解决方案

I've never used MySql, so this is just a theory based on my other database work. When reading the other answers, trying to use REPLACE(), I thought I could post this and get someone with MySql syntax experience a few ideas to make a set base solution.

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

DECLARE @Source table (Texts varchar(50))

INSERT @Source VALUES ('thx guys')

INSERT @Source VALUES ('i think u r 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 u r great i

i think u r great think

i think u r great you

i think u r great are

i think u r great great

thx again thanks

thx again again

u rock you

u rock rock

(11 row(s) affected)

It would be better to use a "real" PK than the actual "Texts" in the query above, but the OP doesn't list many columns in that table, so I use "Texts".

Using SQL Server you need to use a some funky XML syntax to join the rows back together (so I won't show that code, as it doesn't matter), but using MySql's GROUP_CONCAT() you should be able to concatenate the word rows back together into phrase rows.

the code for the (SQL Server) split function and how it works can be found here: SQL Server: Split operation