MySQL可以替换多个字符吗? [英] Can MySQL replace multiple characters?
问题描述
我正在尝试替换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 ;)
这篇关于MySQL可以替换多个字符吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!