MySQL可以替换多个字符吗?

2021-11-20 00:00:00 string sql replace mysql

我正在尝试替换 MySQL 字段中的一堆字符.我知道 REPLACE 函数，但它一次只替换一个字符串.我在手册中看不到任何适当的函数.

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.

推荐答案

您可以链接 REPLACE 函数:

You can chain REPLACE functions:

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

这将打印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

或者使用 JOIN 来替换它们:

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

相关文章