Mysql 简明教程
MySQL - REGEXP_REPLACE() Function
MySQL 中的正则表达式用于搜索操作，不仅可以过滤记录，还可以替换字符串中的模式出现。
Regular expressions in MySQL are used in search operations to not only filter records but also replace the pattern occurrences in a string.
考虑这样一个场景：你注意到 MySQL 数据库中存在大量数据集中拼写错误。现在，你应该在此数据库中更正这些错误的所有出现，而不影响其他数据。正则表达式在这里非常有利。
Consider a scenario where you noticed a spelling error among the huge sets of data present in a MySQL database. Now, you are supposed to correct all occurrences of these errors in this database without disturbing the other data. This is where regular expressions are extremely advantageous.
你可以使用正则表达式来查找相同错误的准确出现并用正确的字符替换它。这是使用 regexp_replace() 函数完成的。
You can use regular expressions to find the accurate occurrences of the same error and replace it with the right characters. This is done using the regexp_replace() function.
MySQL REGEXP_REPLACE() Function
MySQL regexp_replace() 函数用于查找和替换与特定模式匹配的字符串的出现。如果匹配，它将用另一个字符串替换该字符串。如果没有匹配，它将返回原始字符串。如果字符串或模式为 NULL，它将返回 NULL。你可以在此函数中使用正则表达式或简单的字符串作为模式。
The MySQL regexp_replace() function is used to find and replace occurrences of a string that match specific patterns. If there’s a match, it replaces the string with another. If there’s no match, it returns the original string. If the string or pattern is NULL, it returns NULL. You can use a regular expression or a simple string as the pattern in this function.
Syntax
以下是 MySQL regexp_replace() 函数的语法：
Following is the syntax of the MySQL regexp_replace() function −
REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])Parameters
regexp_replace() 函数采用以下参数值：
The regexp_replace() function takes following parameter values −
-
expr: The string in which search is performed
-
pattern: The pattern that is searched in the string
-
repl: The replacement string
此方法还接受以下可选参数：
This method also accepts following optional arguments −
-
pos − Starting position of the search
-
occurrence − Which occurrence of a match to replace. If omitted, the default is 0 so it replaces all occurrences.
-
match_type − A string that specifies how to perform matching.
Example
我们使用 MySQL REGEXP_REPLACE() 函数对一个简单字符串执行了搜索操作，如下所示：
In the following query, we are performing a search operation on a simple string using the MySQL REGEXP_REPLACE() function −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welll')
AS RESULT;从以下输出中我们可以观察到，字符串“Welcome”已被找到并替换为“Welll”：
As we can observe the output below, the string 'Welcome' is found and replaced with 'Welll' −
但如果字符串中没有找到模式，则函数会显示原始字符串。请看以下查询：
But if the pattern is not found in the string, the original string is displayed by the function. Look at the following query −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'H', 'Hi') AS RESULT;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Example
我们还尝试将可选参数作为不区分大小写的匹配（i）传递给该函数。在此，搜索从给定字符串的第 10 个位置开始；由于我们将匹配项值设为 1，因此在第 10 个位置之后，仅第一个字母“t”的匹配项将被替换，无论其大小写如何：
Let us also try to pass optional arguments to this function as case-insensitive matching(i). Here, the search starts from the 10th position in the given string; and as we are passing the occurrence value as 1, only the first occurrence of the letter 't' after 10th position will be replaced irrespective of its case −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 't', 'x', 10, 1, 'i') AS RESULT;Example
以下查询替换给定文本中字符串“is”的所有匹配项：
The following query replaces all the occurrences of the string "is" in the given text −
SELECT REGEXP_REPLACE('This is a sample string', 'is', '@@@@')
As Result;Example
以下查询仅将给定文本中字符串“This”的第一个匹配项替换为“That”：
The following query replaces only the first occurrence of the string "This" in the given text with "That" −
SELECT REGEXP_REPLACE('This is a test and This is another test', '^This', 'That')
As Result;Example
在此，以下查询使用 MySQL REGEXP_REPLACE() 函数，将给定字符串中的单词“wall”或“floor”替换为单词“bed”：
Here, the below query replace the words 'wall' or 'floor' with the word 'bed' in the given string using the MySQL REGEXP_REPLACE() function −
SELECT REGEXP_REPLACE ('Humpty dumpty sat on a wall and slept on the floor', 'wall|floor', 'bed') As Result;Example
以下查询用字符串“drink”替换给定输入字符串中字符串“eat”的第一个匹配项。
The following query replaces the first occurrence of the string "eat" with the string "drink" in the provided input string.
在查询中，第四个参数“1”指定开始搜索的位置，第五个参数“1”是要进行的替换的次数。因此，只将“eat”的第一次匹配项替换为“drink”。
In the query, the fourth parameter "1" specifies the position to start the search and the fifth parameter "1" is the number of replacements to be made. Therefore, only the first occurrence of "eat" is replaced with "drink".
SELECT REGEXP_REPLACE('eat sleep repeat and eat', 'eat', 'drink', 1, 1)
As Result;Example
如果传递给该函数的头两个参数中的任何一个为 NULL，该函数将返回 NULL。在此，我们用 NULL 作为字符串参数。
If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing NULL to the string parameter.
SELECT REGEXP_REPLACE(NULL, 'value', 'test') As Result;输出如下：
Following is the output −
如果我们用 NULL 作为模式参数，则返回 NULL 作为输出。
If we pass NULL to the pattern parameter, it returns NULL as output.
SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, 'sample')
As Result;以上查询的输出如下所示：
The output for the query above is produced as given below −
如果您将空字符串作为替换字符串传递，则该函数将返回 NULL。
If you pass empty string as the replacement string, this function returns NULL.
SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, '')
As Result;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Example
在另一个示例中，我们尝试使用 REGEXP_REPLACE() 函数对名为 CUSTOMERS 的数据库表执行搜索操作。首先，我们使用以下查询创建表 −
In another example, let us try to perform a search operation on a database table named CUSTOMERS using the REGEXP_REPLACE() function. First of all, let us create the table using the following query −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下 SELECT 语句以显示 CUSTOMERS 表的所有记录 −
Execute the following SELECT statement to display all the records of CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
以下查询使用 REGEXP_REPLACE() 函数更新 person_tbl 表中的 NAME 列。它查找以字母“A”开头的名称，并用“An”替换该“A”。
The following query uses the REGEXP_REPLACE() function to update the NAME column in the person_tbl table. It looks for names that start with the letter 'A' and replaces that 'A' with 'An'.
SELECT REGEXP_REPLACE(NAME, '^A', 'An') AS Result FROM CUSTOMERS;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
但是，如果在表的任何记录中找不到该模式，函数将显示表的原始值。查看以下查询 −
But if the pattern is not found in any record of the table, the original values of the table are displayed by the function. Look at the following query −
SELECT REGEXP_REPLACE(ADDRESS, '^Z', 'P') AS RESULT FROM CUSTOMERS;ADDRESS 列中没有以字母“Z”开头的记录。因此，它将原始记录作为输出返回 −
There is no record in ADDRESS column that starts with letter 'Z'. So, it returned the original records as output −
以下查询使用 REGEXP_REPLACE 函数将 CUSTOMERS 表的 ADDRESS 列中的第二个字母“r”替换为“R” −
The following query is using the REGEXP_REPLACE function to replace the second occurrence of the letter 'r' with 'R' in the ADDRESS column of the CUSTOMERS table −
SELECT REGEXP_REPLACE(ADDRESS, 'r', 'R', 2, 0, 'c')
AS RESULT FROM CUSTOMERS;正如我们在输出中看到的，记录“Hyderabad”和“Indore”中都有字母“r”。而且它们被“R”替换 −
As we can see in the output, the records 'Hyderabad' and 'Indore' has letter 'r' in it. And they are replaced by 'R' −
REGEXP_REPLACE() Funcion Using a Client Program
我们还可以使用客户端程序执行 MySQL REGEXP_REPLACE 函数来查找和替换与特定模式匹配的字符串的出现。
We can also perform the MySQL REGEXP_REPLACE function using the client programs to find and replace occurrences of a string that match specific patterns.