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://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_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'
打印页面
上一页
下一页
广告