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

但是，如果在字串中找不到模式，則函式將顯示原始字串。請檢視以下查詢 -

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

執行給定查詢後，輸出將顯示如下 -

示例

我們還可以嘗試將可選引數作為不區分大小寫的匹配 (i) 傳遞給此函式。在這裡，搜尋從給定字串中的第 10 個位置開始；並且由於我們正在將 occurrence 值傳遞為 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 傳遞給模式引數，它將返回 NULL 作為輸出。

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

上面查詢的輸出如下所示 -

如果將空字串作為替換字串傳遞，則此函式將返回 NULL。

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

執行給定查詢後，輸出將顯示如下 -

示例

舉個例子，讓我們嘗試在名為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表：

以下查詢使用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"
});
 //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://: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!'