I'm trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can't see any appropriate functions in the manual.
Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation.
You can chain REPLACE functions:
select replace(replace('hello world','world','earth'),'hello','hi')
This will print
You can even use subqueries to replace multiple strings!
select replace(london_english,'hello','hi') as warwickshire_english from ( select replace('hello world','world','earth') as london_english ) sub
Or use a JOIN to replace them:
select group_concat(newword separator ' ') from ( select 'hello' as oldword union all select 'world' ) orig inner join ( select 'hello' as oldword, 'hi' as newword union all select 'world', 'earth' ) trans on orig.oldword = trans.oldword
I'll leave translation using common table expressions as an exercise for the reader ;)