使用另一个表中的数据在MySQL中查找和替换字符串 [英] Find and replace string in MySQL using data from another table

问题描述

我有两个MySQL表，我想使用另一个表中的数据查找并替换其中的文本字符串.

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

表texts:

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

表dictionary:

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

我希望SQL能够遍历消息中的每一行，并用good_spelling替换bad_spelling的每个实例，然后对bad_spelling和good_spelling的所有配对执行此操作.

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.

我得到的最接近的是:

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

但这只会将"thx"更改为"thanks"(两行)，并且不会继续将"u"替换为"you"或将"r"替换为"are".

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

有什么想法如何使它使用replace语句中字典中的所有行?

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

PS忘了提到这是一个小例子，实际上，我将有很多查找/替换对，随着时间的推移，它们可能会增加.

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.

推荐答案

我从未使用过MySql，所以这只是基于其他数据库工作的理论.在阅读其他答案时，尝试使用REPLACE()，我想我可以发布此内容，并让具有MySql语法的人有一些想法来制定一套基本的解决方案.

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.

下面是一些SQL Server代码，可以为您完成大部分工作:

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

输出:

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)

在上面的查询中，使用真实的" PK比使用实际的文本"会更好，但是OP不会在该表中列出很多列，因此我使用文本".

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".

使用SQL Server，您需要使用一些时髦的XML语法将行重新连接在一起(因此，我不会显示该代码，因为这无关紧要)，但是使用MySql的GROUP_CONCAT()，您应该能够将单词行重新组合成短语行.

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.

(SQL Server)拆分功能的代码及其工作方式可以在这里找到: SQL Server:拆分操作

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

这篇关于使用另一个表中的数据在MySQL中查找和替换字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！