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

结果
Welll To Tutorialspoint!

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

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

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

结果
Welcome To Tutorialspoint!

示例

我们还可以尝试将可选参数作为不区分大小写的匹配 (i) 传递给此函数。在这里,搜索从给定字符串中的第 10 个位置开始;并且由于我们正在将 occurrence 值传递为 1,因此第 10 个位置之后的字母“t”的第一次出现将被替换,无论其大小写如何 -

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

输出

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

结果
Welcome To xutorialspoint!

示例

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

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

输出

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

结果
Th@@@@ @@@@ a sample string

示例

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

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

输出

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

结果
That is a test and This is another test

示例

在这里,下面的查询使用 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;

输出

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

结果
Humpty dumpty sat on a bed and slept on the bed

示例

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

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

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

输出

以下是输出 -

结果
drink sleep repeat and eat

示例

如果传递给此函数的前两个参数中的任何一个为 NULL,则此函数返回 NULL。在这里,我们正在将 NULL 传递给字符串参数。

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

以下是输出 -

结果
NULL

如果我们将 NULL 传递给模式参数,它将返回 NULL 作为输出。

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

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

结果
NULL

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

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

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

结果
NULL

示例

举个例子,让我们尝试在名为CUSTOMERS的数据库表上执行搜索操作,使用REGEXP_REPLACE()函数。首先,让我们使用以下查询创建表:

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

ID NAME AGE ADDRESS SALARY
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

以下查询使用REGEXP_REPLACE()函数更新person_tbl表中的NAME列。它查找以字母“A”开头的名称,并将该“A”替换为“An”。

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

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

结果
Ramesh
Khilan
Kaushik
Chaitali
Hardik
Komal
Muffy

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

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

ADDRESS列中没有以字母“Z”开头的记录。因此,它返回原始记录作为输出:

结果
Ahmedabad
Delhi
Kota
Mumbai
Bhopal
Hyderabad
Indore

以下查询使用REGEXP_REPLACE函数将CUSTOMERS表ADDRESS列中字母“r”的第二个出现替换为“R”:

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

从输出中可以看到,“Hyderabad”和“Indore”记录中包含字母“r”。并且它们被替换为“R”:

结果
Ahmedabad
Delhi
Kota
Mumbai
Bhopal
HydeRabad
IndoRe

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

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

语法

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

要使用PHP程序通过MySQL查询匹配特定模式并替换为另一个字符串,我们需要使用mysqli函数query()执行“SELECT”语句,如下所示:

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

要使用Node.js程序通过MySQL查询匹配特定模式并替换为另一个字符串,我们需要使用mysql2库的query()函数执行“SELECT”语句,如下所示:

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

要使用Java程序通过MySQL查询匹配特定模式并替换为另一个字符串,我们需要使用JDBC函数executeUpdate()执行“SELECT”语句,如下所示:

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

要使用Python程序通过MySQL查询匹配特定模式并替换为另一个字符串,我们需要使用MySQLConnector/Pythonexecute()函数执行“SELECT”语句,如下所示:

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"
});
 //Connecting to 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://127.0.0.1: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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_replace_query = f"SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT;"
cursorObj.execute(regexp_replace_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
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!'
广告