MySQL - REGEXP_SUBSTR() 函式

Table of content
上一頁
下一頁

MySQL 中的正則表示式用於搜尋操作，以過濾和檢索與指定模式匹配的資料庫表中的記錄。

這個在資料集中檢測模式的過程稱為模式匹配。每當資料被認為具有相似的特徵時，它都是有幫助的；在這種情況下，你可能會找到資料及其所有出現情況中的模式。模式匹配通常在原始資料上執行，以確保其語法正確。

同樣，在 MySQL 中，執行模式匹配以檢查資料是否正確儲存。如果不是，則使用正則表示式的函式檢測（有時替換）不正確的模式。regexp_substr() 函式用於從一組資料中檢測指定的模式。

MySQL REGEXP_SUBSTR() 函式

MySQL regexp_substr() 函式用於資料庫中的模式匹配。此函式返回與指定模式匹配的字串的子字串，如果不存在匹配項或字串或模式為 NULL，則返回 NULL。在這裡，模式定義為擴充套件正則表示式或普通字串。

語法

以下是 MySQL regexp_substr() 函式的語法：

REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]])

引數

regexp_substr() 函式採用以下引數值：

  • expr: 執行搜尋的字串

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

此方法還接受以下可選引數：

  • pos: 搜尋的起始位置

  • occurrence: 要替換的匹配項的出現次數。如果省略，則預設為 1，因此它僅檢索第一次出現。

  • match_type: 指定如何執行匹配的字串。

示例

以下示例演示了在簡單的字串 'Welcome To Tutorialspoint!' 上使用 MySQL regexp_substr() 函式，使用方法如下：

SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT;

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

結果
We

如果字串中不存在模式，則結果返回為 NULL。讓我們嘗試使用以下查詢在相同的字串 'Welcome To Tutorialspoint!' 中搜索模式 'Hi'：

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

以下是輸出：

結果
NULL

示例

讓我們將 5 作為值傳遞給 'pos' 引數，以便搜尋從給定字串的第 5 個位置開始；並且由於我們將 occurrence 值作為 2 傳遞，因此將檢索第 5 個位置之後模式 'to' 的第二次出現，而不考慮其大小寫：

SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'To', 5, 2, 'i')
AS RESULT;

輸出

當我們執行上述查詢時，獲得的輸出如下：

結果
to

示例

如果傳遞給此函式的前兩個引數中的任何一個為 NULL，則此函式將返回 NULL。在下面的查詢中，我們將 NULL 傳遞給表示式引數。

SELECT REGEXP_SUBSTR(NULL, 'value');

執行給定程式後，輸出顯示如下：

REGEXP_SUBSTR(NULL, 'value')
NULL

在這裡，我們將 NULL 作為要搜尋的模式傳遞：

SELECT REGEXP_SUBSTR('Welcome to Tutorialspoint', NULL)
AS Result;

以下是輸出：

結果
NULL

示例

在下面的查詢中，我們使用 REGEXP_SUBSTR() 函式對名為 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 );

執行以下查詢以顯示 CUSTOMERS 表的所有記錄：

Select * from CUSTOMERS;

以下是 CUSTOMERS 表：

ID 姓名 年齡 地址 薪資
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_SUBSTR()函式從“姓名”列的值中檢索以'Ra'開頭的子字串。

SELECT REGEXP_SUBSTR(NAME, '^Ra') AS RESULT FROM CUSTOMERS;

輸出

如下所示，只有“姓名”列的第一條記錄包含子字串'Ra'：

結果
Ra
NULL
NULL
NULL
NULL
NULL
NULL

使用客戶端程式的 REGEXP_SUBSTR() 函式

我們也可以使用客戶端程式執行MySQL REGEXP_SUBSTR()函式，以從一組資料中檢測指定的模式。

語法

以下是此操作在各種程式語言中的語法：

PHP NodeJS Java Python

要透過PHP程式從MySQL資料庫中檢索與特定模式匹配的字串的一部分，我們需要使用mysqli函式query()執行'SELECT'語句，如下所示：

$sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
$mysqli->query($sql);

要透過Node.js程式從MySQL資料庫中檢索與特定模式匹配的字串的一部分，我們需要使用mysql2庫的query()函式執行'SELECT'語句，如下所示：

sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
con.query(sql);

要透過Java程式從MySQL資料庫中檢索與特定模式匹配的字串的一部分，我們需要使用JDBC函式executeUpdate()執行'SELECT'語句，如下所示：

String sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
statement.executeQuery(sql);

要透過Python程式從MySQL資料庫中檢索與特定模式匹配的字串的一部分，我們需要使用MySQL Connector/Python的execute()函式執行'SELECT'語句，如下所示：

sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT" 
cursorObj.execute(sql)

示例

以下是程式：

PHP NodeJS Java Python
$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_SUBSTR('Welcome To Tutorialspoint!', 'We') 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: We

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_SUBSTR('Welcome To Tutorialspoint!', 'We') 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: 'We' } ]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_substr {
    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_SUBSTR('Welcome To Tutorialspoint!', 'We') 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: We

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_substr_query = f"SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT"
cursorObj.execute(regexp_substr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_SUBSTR() Function:")
for row in results:
    result = row[0]
    print(f"The extracted substring is: '{result}'")
cursorObj.close()
connection.close()

輸出

獲得的輸出如下所示：

Result of REGEXP_SUBSTR() Function:
The extracted substring is: 'We'
列印頁面
上一頁
下一頁
廣告