MySQL 可以替换多个字符吗？

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

我可以一次替换或删除多个字符串吗？例如，我需要用破折号替换空格，并删除其他标点符号。

117905 次浏览
小开
最佳答案

可以链接 REPLACE 函数:

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

这将打印 hi earth。

您甚至可以使用子查询来替换多个字符串！

select replace(london_english,'hello','hi') as warwickshire_english
from (
select replace('hello world','world','earth') as london_english
) sub

或者使用 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

我将把使用常用表格表达式的翻译留给读者作为练习;)

小开

级联是 mysql 用于多个字符替换的唯一简单而直接的解决方案。

UPDATE table1
SET column1 = replace(replace(REPLACE(column1, '\r\n', ''), '<br />',''), '<\r>','')
小开

REPLACE 可以很好地替换字符串中出现的所有字符或短语。但是当清理标点符号时，你可能需要寻找模式——例如，一个单词中间或者句号后面的一系列空格或字符。如果是这种情况，那么正则表达式替换函数将会更加强大。

更新: 如果使用 MySQL 版本8 + ，将提供一个 REGEXP_REPLACE函数，可以按如下方式调用:

SELECT txt,
REGEXP_REPLACE(REPLACE(txt, ' ', '-'),
'[^a-zA-Z0-9-]+',
'') AS `reg_replaced`
FROM test;

参见 这个 DB Fiddle 在线演示。

上一页答案 -只有在使用版本8之前的 MySQL 版本时才继续读取: 。

坏消息是 MySQL 不提供这样的东西，但好消息是有可能提供一个变通方法——参见 这篇博文。

我可以一次替换或删除多个字符串吗? 例如，我需要 用破折号代替空格和删除其他标点符号。

以上可以通过正则表达式替换器和标准 REPLACE功能的组合来实现。在 这个在线 Rextester 演示中可以看到它的作用。

SQL (不包括简洁的函数代码):

SELECT txt,
reg_replace(REPLACE(txt, ' ', '-'),
'[^a-zA-Z0-9-]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
小开

我一直在使用 Lib _ mysqludf _ preg来做这个，它允许你:

在 MySQL 中直接使用 PCRE 正则表达式

安装了这个库之后，您可以执行以下操作:

SELECT preg_replace('/(\\.|com|www)/','','www.example.com');

也就是说:

example
小开

在 php 上

$dataToReplace = [1 => 'one', 2 => 'two', 3 => 'three'];
$sqlReplace = '';
foreach ($dataToReplace as $key => $val) {
$sqlReplace = 'REPLACE(' . ($sqlReplace ? $sqlReplace : 'replace_field') . ', "' . $key . '", "' . $val . '")';
}
echo $sqlReplace;

结果

REPLACE(
REPLACE(
REPLACE(replace_field, "1", "one"),
"2", "two"),
"3", "three");
小开 
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')
);
小开 
UPDATE schools SET
slug = lower(name),
slug = REPLACE(slug, '|', ' '),
slug = replace(slug, '.', ' '),
slug = replace(slug, '"', ' '),
slug = replace(slug, '@', ' '),
slug = replace(slug, ',', ' '),
slug = replace(slug, '\'', ''),
slug = trim(slug),
slug = replace(slug, ' ', '-'),
slug = replace(slug, '--', '-');

更新学校设置 Slug = place (slug,’——’,’-’) ;

小开

如果您正在使用 MySQL 版本8 + ，那么下面的内置函数可能会更好地帮助您。

绳子 替换 输出
w"w\'w. ex%a&m:p l–e.c)o(m "'%&:)(– Www.example.com

MySQL 查询:

SELECT REGEXP_REPLACE('`w"w\'w.    ex%a&m:p     l–e.c)o(m`', '[("\'%[:blank:]&:–)]', '');

几乎所有的窃听字符-

SELECT REGEXP_REPLACE(column, '[\("\'%[[:blank:]]&:–,#$@!;\\[\\]\)<>\?\*\^]+','')

现实生活中的场景。

我不得不更新所有的文件名已经保存在’演示’与特殊字符。

SELECT * FROM demo;

| uri                                                                          |
|------------------------------------------------------------------------------|
| private://webform/applicant_details/129/offers   upload  winners .png        |
| private://webform/applicant_details/129/student : class & teacher data.pdf   |
| private://webform/applicant_details/130/tax---user's---data__upload.pdf      |
| private://webform/applicant_details/130/Applicant Details _ report_0_2.pdf   |
| private://webform/applicant_details/131/india&asia%population  huge.pdf      |

测试案例-

该表在文件名中有多行带有特殊字符。

建议:

要删除文件名中的所有特殊字符，使用 A-Z、 A-Z、0-9、点和下划线，并使用较低的文件名。

预期结果是:

| uri                                                                          |
|------------------------------------------------------------------------------|
| private://webform/applicant_details/129/offers_upload_winners_.png           |
| private://webform/applicant_details/129/student_class_teacher_data.pdf       |
| private://webform/applicant_details/130/tax_user_s_data_upload.pdf           |
| private://webform/applicant_details/130/applicant_details_report_0_2.pdf     |
| private://webform/applicant_details/131/india_asia_population_huge.pdf       |

好吧，我们一步一步来

1st  - let's find the file name
2nd  - run all the find replace on that file name part only
3rd  - replace the new file name with an old one

我们要怎么做？

为了更好地理解，让我们把整个动作分成几块。

下列函式只会从完整路径提取档案名称，例如「申请人详情 _ report _ 0 _ 2. pdf 」

SELECT                      -- MySQL SELECT statement
SUBSTRING_INDEX           -- MySQL built-in function
(                         -- Function start Parentheses
uri,                    -- my table column
'/',                    -- delimiter (the last / in full path; left to right ->)
-1                      -- start from the last and find the 1st one (from right to left <-)
)                       -- Function end Parentheses
from                        -- MySQL FROM statement
demo;                     -- My table name

# 1查询结果

| uri                                |
|------------------------------------|
| offers   upload  winners .png      |
| student : class & teacher data.pdf |
| tax---user's---data__upload.pdf    |
| Applicant Details _ report_0_2.pdf |
| india&asia%population  huge.pdf    |

现在我们必须在生成的文件名结果中查找和替换。

SELECT
REGEXP_REPLACE(                       -- MySQL REGEXP_REPLACE built-in function   (string, pattern, replace)
SUBSTRING_INDEX(uri, '/', -1),      -- File name only
'[^a-zA-Z0-9_.]+',                  -- Find everything which is not a-z, A-Z, 0-9, . or _.
'_'                                 -- Replace with _
) AS uri                              -- Give a alias column name for whole result
from
demo;

# 2查询结果

| uri                                |
|------------------------------------|
| offers_upload_winners_.png         |
| student_class_teacher_data.pdf     |
| tax_user_s_data__upload.pdf        |
| Applicant_Details___report_0_2.pdf |
| india_asia_population_huge.pdf     |

仅供参考-模式中的 Last’+’用于重复单词，如——-或多个空格’，请注意下面正则表达式模式中没有’+’的结果。

SELECT
REGEXP_REPLACE(                       -- MySQL REGEXP_REPLACE built-in function   (string, pattern, replace)
SUBSTRING_INDEX(uri, '/', -1),      -- File name only
'[^a-zA-Z0-9_.]',                   -- Find everything which is not a-z, A-Z, 0-9, . or _.
'_'                                 -- Replace with _
) AS uri                              -- Give a alias column name for whole result
from
demo;

# 3查询结果

| uri                                |
|------------------------------------|
| offers___upload__winners_.png      |
| student___class___teacher_data.pdf |
| tax___user_s___data__upload.pdf    |
| Applicant_Details___report_0_2.pdf |
| india_asia_population__huge.pdf    |

现在，我们有了一个没有特殊字符的文件名(。及 _ 准许)。但问题是文件名仍然是大写字母，还有多个下划线。

让我们先降低文件名。

SELECT
LOWER(
REGEXP_REPLACE(
SUBSTRING_INDEX(uri, '/', -1),
'[^a-zA-Z0-9_.]',
'_'
)
) AS uri
from
demo;

查询结果

| uri                                |
|------------------------------------|
| offers_upload_winners_.png         |
| student_class_teacher_data.pdf     |
| tax_user_s_data__upload.pdf        |
| applicant_details___report_0_2.pdf |
| india_asia_population_huge.pdf     |

Now everything is in lower case but underscores are still there. So we will wrap the whole REGEX.. with one more REGEX..

SELECT
LOWER(
REGEXP_REPLACE(                     -- this wrapper will solve the multiple underscores issue
REGEXP_REPLACE(
SUBSTRING_INDEX(uri, '/', -1),
'[^a-zA-Z0-9_.]+',
'_'
),
'[_]+',                           -- if 1st regex action has multiple __ then find it
'_'                               -- and replace them with single _
)
) AS uri
from
demo;

# 5查询结果

| uri                              |
|----------------------------------|
| offers_upload_winners_.png       |
| student_class_teacher_data.pdf   |
| tax_user_s_data_upload.pdf       |
| applicant_details_report_0_2.pdf |
| india_asia_population_huge.pdf   |

恭喜! 我们已经找到了我们要找的东西。现在更新时间! 耶! ！

UPDATE                                      -- run a MySQL UPDATE statement
demo                                      -- tell MySQL to which table you want to update
SET                                         -- put SET statement to set the updated values in desire column
uri = REPLACE(                            -- tell MySQL to which column you want to update,
-- I am also putting REPLACE function to replace existing values with new one
-- REPLACE (string, replace, with-this)
uri,                                    -- my column to replace
SUBSTRING_INDEX(uri, '/', -1),          -- my file name part "Applicant Details _ report_0_2.pdf"
-- without doing any action
LOWER(                                  -- "applicant_details_report_0_2.pdf"
REGEXP_REPLACE(                       -- "Applicant_Details_report_0_2.pdf"
REGEXP_REPLACE(                     -- "Applicant_Details___report_0_2.pdf"
SUBSTRING_INDEX(uri, '/', -1),    -- "Applicant Details _ report_0_2.pdf"
'[^a-zA-Z0-9_.]+',
'_'
),
'[_]+',
'_'
)
)
);

并在后和 UPDATE 查询，结果将是这样的。

| uri                                                                      |
|--------------------------------------------------------------------------|
| private://webform/applicant_details/152/offers_upload_winners_.png       |
| private://webform/applicant_details/153/student_class_teacher_data.pdf   |
| private://webform/applicant_details/153/tax_user_s_data_upload.pdf       |
| private://webform/applicant_details/154/applicant_details_report_0_2.pdf |
| private://webform/applicant_details/154/india_asia_population_huge.pdf   |

示例数据脚本

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`uri` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The S3 URI of the file.',
`filesize` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'The size of the file in bytes.',
`timestamp` int unsigned NOT NULL DEFAULT '0' COMMENT 'UNIX timestamp for when the file was added.',
`dir` int NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether or not this object is a directory.',
`version` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_bin DEFAULT '' COMMENT 'The S3 VersionId of the object.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `demo` (`uri`, `filesize`, `timestamp`, `dir`, `version`) VALUES
('private://webform/applicant_details/152/offers   upload  winners .png', 14976905, 1658397516, 0, ''),
('private://webform/applicant_details/153/student : class & teacher data.pdf', 0, 1659525447, 1, ''),
('private://webform/applicant_details/153/tax---user\'s---data__upload.pdf', 98449, 1658397516, 0, ''),
('private://webform/applicant_details/154/Applicant Details _ report_0_2.pdf', 0, 1659525447, 1, ''),
('private://webform/applicant_details/154/india&asia%population  huge.pdf', 13301, 1658397517, 0, '');

非常感谢:

MySQL: 选择，更新，更换，子串 _ 索引，低一点，< a href = “ https://dev.MySQL.com/doc/refman/8.0/en/REGEXP.html # function _ REGEXP-place”rel = “ nofollow norefrer”> REGEXP _ REPLACE

MySQL Query Formatter: 感谢 CodeBeautify提供这样一个非常棒的工具。


提交答案