Find and replace string in MySQL using data from another table

Posted by Charlie on Stack Overflow See other posts from Stack Overflow or by Charlie
Published on 2010-03-24T17:13:58Z Indexed on 2010/03/24 17:33 UTC
Read the original article Hit count: 423

Filed under:
|

Hi, sorry for formatting this wonky but hope you can understand it.

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

Texts - one column: messages
'thx guys'
'i think u r great'
'thx again'
' u rock'

Dictionary - two columns: 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 2 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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql