MySQL可以替换多个字符吗？

Question

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

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

原文由 DisgruntledGoat 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以链接 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

我将使用公用表表达式作为练习留给读者的翻译；）

原文由 Andomar 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

如果您使用的是 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      |

测试用例 -

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

建议：

要从文件名中删除所有特殊字符，并使用 az、AZ、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     |

仅供参考 - 模式中的最后一个“+”用于重复单词，如 ---- 或多个空格“”，请注意以下正则表达式模式中没有“+”的结果。

 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;

#4 查询结果

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

现在一切都是小写的，但下划线仍然存在。因此，我们将用另外一个 REGEX.. 包装整个 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 Query 之后，结果会是这样的。

 | 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： 选择、 更新、 替换、 SUBSTRING_INDEX 、 降低、 REGEXP_REPLACE

MySQL Query Formatter： 感谢 CodeBeautify 提供了这么棒的工具。

原文由 Nono 发布，翻译遵循 CC BY-SA 4.0 许可协议