Find and replace string in MySQL using data from another table
- by Charlie
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.
