MySQL - REGEXP_REPLACE() 函数

• MySQL REGEXP_REPLACE() 函数
• 使用客户端程序的 REGEXP_REPLACE() 函数

MySQL 中的正则表达式不仅可用于搜索，还可以用于过滤记录，并替换字符串中出现的模式。

设想这样一个场景:您在 MySQL 数据库中的大量数据中发现了一个拼写错误。现在，您需要在不影响其他数据的情况下，更正该数据库中所有出现的拼写错误。这时，正则表达式就显得尤为重要了。

您可以使用正则表达式查找相同错误的准确位置，并用正确的字符替换它。这可以通过 regexp_replace() 函数实现。

MySQL REGEXP_REPLACE() 函数

MySQL regexp_replace() 函数用于查找并替换与特定模式匹配的字符串。如果匹配，则用另一个字符串替换该字符串。如果不匹配，则返回原始字符串。如果字符串或模式为 NULL，则返回 NULL。您可以使用正则表达式或简单字符串作为此函数中的模式。

语法

以下是 MySQL regexp_replace() 函数的语法 -

REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])

参数

regexp_replace() 函数接受以下参数值 -

• expr: 执行搜索的字符串

• pattern: 在字符串中搜索的模式

• repl: 替换字符串

此方法还接受以下可选参数参数 −

• pos − 搜索的起始位置

• occurrence − 匹配项出现的位置。如果省略，默认值为 0，即替换所有匹配项。

• match_type − 指定如何执行匹配的字符串。

示例

在下面的查询中，我们使用 MySQL REGEXP_REPLACE() 函数对简单字符串执行搜索操作 −

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welll')
AS RESULT;

正如我们观察到的下面的输出，字符串"Welcome"被找到并替换为"Welll"-

但是，如果在字符串中找不到该模式，函数将显示原始字符串。请看以下查询 -

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'H', 'Hi') AS RESULT;

执行给定查询后，输出显示如下 -

示例

我们尝试将可选参数传递给此函数，使其不区分大小写匹配 (i)。此处，搜索从给定字符串的第 10 个位置开始;由于我们将出现次数设置为 1，因此只有第 10 个位置之后第一次出现的字母"t"会被替换，无论其大小写如何 -

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 't', 'x', 10, 1, 'i') AS RESULT;

输出

上述程序的输出如下所示 -

示例

以下查询将替换给定文本中所有出现的字符串"is" -

SELECT REGEXP_REPLACE('This is a sample string', 'is', '@@@@')
As Result;

输出

执行给定查询后，输出结果如下 -

示例

以下查询仅将给定文本中第一次出现的字符串"This"替换为"That"-

SELECT REGEXP_REPLACE('This is a test and This is another test', '^This', 'That')
As Result;

输出

上述查询的输出如下所示 -

示例

以下查询使用 MySQL REGEXP_REPLACE() 函数将给定字符串中的单词"wall"或"floor"替换为单词"bed" -

SELECT REGEXP_REPLACE ('Humpty dumpty sat on a wall and slept on the floor', 'wall|floor', 'bed') As Result;

输出

执行给定程序后，输出显示如下 -

示例

以下查询将提供的输入字符串中第一次出现的字符串"eat"替换为字符串"drink"。

在查询中，第四个参数"1"指定开始搜索的位置，第五个参数"1"是要进行的替换次数。因此，只有第一次出现的"eat"被替换为"drink"。

SELECT REGEXP_REPLACE('eat sleep repeat and eat', 'eat', 'drink', 1, 1)
As Result;

输出

以下是输出 -

示例

如果传递给此函数的前两个参数中有一个为 NULL，则此函数返回 NULL。此处，我们将 NULL 传递给字符串参数。

SELECT REGEXP_REPLACE(NULL, 'value', 'test') As Result;

以下是输出 -

如果我们将 NULL 传递给 pattern 参数，则返回 NULL 作为输出。

SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, 'sample')
As Result;

上述查询的输出如下所示 -

如果传递空字符串作为替换字符串，此函数将返回 NULL。

SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, '')
As Result;

执行给定查询后，输出结果如下:-

示例

在另一个示例中，我们尝试使用 REGEXP_REPLACE() 函数对名为 CUSTOMERS 的数据库表执行搜索操作。首先，我们使用以下查询创建表:-

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 条记录插入到上面创建的表中 -

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 表的所有记录 -

Select * from CUSTOMERS;

以下是 CUSTOMERS 表 -

以下查询使用 REGEXP_REPLACE() 函数更新 person_tbl 表中的 NAME 列。它会查找以字母"A"开头的姓名，并将"A"替换为"An"。

SELECT REGEXP_REPLACE(NAME, '^A', 'An') AS Result FROM CUSTOMERS;

执行给定查询后，输出结果如下:-

但是，如果在表中的任何记录中都找不到该模式，则该函数将显示表的原始值。请看以下查询 -

SELECT REGEXP_REPLACE(ADDRESS, '^Z', 'P') AS RESULT FROM CUSTOMERS;

ADDRESS 列中没有以字母"Z"开头的记录。因此，它返回原始记录作为输出 -

以下查询使用 REGEXP_REPLACE 函数将 CUSTOMERS 表的 ADDRESS 列中第二次出现的字母 'r' 替换为 'R' -

SELECT REGEXP_REPLACE(ADDRESS, 'r', 'R', 2, 0, 'c')
AS RESULT FROM CUSTOMERS;

从输出结果可以看出，"Hyderabad"和"Indore"记录中包含字母 'r'。它们被替换为 'R' -

使用客户端程序的 REGEXP_REPLACE() 函数

我们还可以使用客户端程序执行 MySQL REGEXP_REPLACE 函数，查找并替换与特定模式匹配的字符串。

语法

以下是此操作在各种编程语言中的语法 -

$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
$mysqli->query($sql);

sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
con.query(sql);

String sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
statement.executeQuery(sql);

sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')"
cursorObj.execute(sql)

示例

以下是程序 -

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row['RESULT']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: Welcom To Tutorialspoint!

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});     

Select query executed successfully..!
Table records:
[ { RESULT: 'Welcome To Tutorialspoint!' } ]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_replace {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Result: Welcom To Tutorialspoint!

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
regexp_replace_query = f"SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT;"
cursorObj.execute(regexp_replace_query)
# 获取所有结果
results = cursorObj.fetchall()
# 显示结果
print("Result of REGEXP_REPLACE() Function:")
for row in results:
    result = row[0]
    print(f"The modified string is: '{result}'")
cursorObj.close()
connection.close()    

Result of REGEXP_REPLACE() Function:
The modified string is: 'Welcom To Tutorialspoint!'