MySQL MySQL可以替换多个字符吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1671040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can MySQL replace multiple characters?
提问by DisgruntledGoat
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.
我正在尝试替换 MySQL 字段中的一堆字符。我知道 REPLACE 函数，但它一次只替换一个字符串。我在手册中看不到任何适当的功能。
Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation.
我可以一次替换或删除多个字符串吗？例如，我需要用破折号替换空格并删除其他标点符号。
回答by Andomar
You can chain REPLACE functions:
您可以链接 REPLACE 函数：
select replace(replace('hello world','world','earth'),'hello','hi')
This will print hi earth.
这将打印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:
或者使用 JOIN 来替换它们：
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 ;)
我将使用常用表表达式进行翻译作为读者练习；)
回答by Anurag
Cascading is the only simple and straight-forward solution to mysql for multiple character replacement.
级联是 mysql 唯一简单直接的多字符替换解决方案。
UPDATE table1
SET column1 = replace(replace(REPLACE(column1, '\r\n', ''), '<br />',''), '<\r>','')
回答by billynoah
I've been using lib_mysqludf_pregfor this which allows you to:
我一直在为此使用lib_mysqludf_preg，它允许您：
Use PCRE regular expressions directly in MySQL
在 MySQL 中直接使用 PCRE 正则表达式
With this library installed you could do something like this:
安装此库后，您可以执行以下操作：
SELECT preg_replace('/(\.|com|www)/','','www.example.com');
Which would give you:
这会给你：
example
回答by asdasd
on php
在 php 上
$dataToReplace = [1 => 'one', 2 => 'two', 3 => 'three'];
$sqlReplace = '';
foreach ($dataToReplace as $key => $val) {
$sqlReplace = 'REPLACE(' . ($sqlReplace ? $sqlReplace : 'replace_field') . ', "' . $key . '", "' . $val . '")';
}
echo $sqlReplace;
result
结果
REPLACE(
REPLACE(
REPLACE(replace_field, "1", "one"),
"2", "two"),
"3", "three");
回答by Steve Chambers
REPLACEdoes a good simple job of replacing characters or phrases everywhere they appear in a string. But when cleansing punctuation you may need to look for patterns - e.g. a sequence of whitespace or characters at particular parts of the text, e.g. in the middle of a word or after a full stop. If that's the case, a regular expression replace function would be much more powerful. The bad news is MySQL doesn't provide such a thingbut the good news is it's possible to provide a workaround - see this blog post.
REPLACE在替换字符串中出现的任何字符或短语方面做得非常简单。但是在清理标点符号时，您可能需要寻找模式——例如文本特定部分的空白或字符序列，例如在单词中间或句号之后。如果是这种情况，正则表达式替换功能会更强大。坏消息是MySQL 没有提供这样的东西，但好消息是可以提供一种解决方法 - 请参阅此博客文章。
Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation.
我可以一次替换或删除多个字符串吗？例如，我需要用破折号替换空格并删除其他标点符号。
The above can be achieved with a combination of the regular expression replacer and the standard REPLACEfunction. It can be seen in action in this online Rextester demo.
以上可以通过正则表达式替换器和标准REPLACE函数的组合来实现。在这个在线 Rexester 演示中可以看到它的实际效果。
SQL (excluding the function code for brevity):
SQL （为简洁起见，不包括函数代码）：
SELECT txt,
reg_replace(REPLACE(txt, ' ', '-'),
'[^a-zA-Z0-9-]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
回答by Frank Barthold
CREATE FUNCTION IF NOT EXISTS num_as_word (name TEXT) RETURNS TEXT RETURN
(
SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(IFNULL(name, ''),
'1', 'one'),
'2', 'two'),
'3', 'three'),
'4', 'four'),
'5', 'five'),
'6', 'six'),
'7', 'seven'),
'8', 'eight'),
'9', 'nine')
);
