mysqlsqlstringreplace

Can MySQL replace multiple characters?


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.


Solution

  • You can chain REPLACE functions:

    select replace(replace('hello world','world','earth'),'hello','hi')
    

    This will print hi earth.

    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 ;)