Mysql 简明教程
MySQL - REGEXP_SUBSTR() Function
在 MySQL 中，正则表达式用于在搜索操作中筛选和提取与指定模式匹配的数据库表中的记录。
Regular expressions in MySQL are used in search operations to filter and retrieve records from a database table that match specified patterns.
在数据集中检测模式的过程称为模式匹配。每当数据被认为具有相似特征时，这都非常有帮助；在这种情况下，您可能在数据中找到一个模式及其所有出现。模式匹配通常在原始数据上执行，以确保其语法正确。
This process of detecting patterns in a set of data is known as pattern matching. It is helpful whenever the data is considered to have similar characteristics; in such cases, you might locate a pattern in the data and all its occurrences. Pattern matching is usually performed on raw data to make sure it is syntactically correct.
类似地，在 MySQL 中，执行模式匹配以检查数据是否正确存储。如果没有，则使用正则表达式的函数检测（有时替换）不正确的模式。 regexp_substr() 函数用于从一组数据中检测指定的模式。
Similarly, in MySQL, pattern matching is performed to check whether the data is correctly stored or not. If not, the incorrect pattern is detected (and sometimes replaced) using functions of regular expressions. The regexp_substr() function is used to detect specified patterns from a set of data.
MySQL REGEXP_SUBSTR() Function
MySQL regexp_substr() 函数用于在数据库中进行模式匹配。此函数返回与指定模式匹配的字符串的子字符串，如果没有匹配项，或者字符串或模式为空，则返回 NULL。此处，模式被定义为扩展的正则表达式或只是普通字符串。
The MySQL regexp_substr() function is used for pattern matching in a database. This function returns the substring of a string that matches the pattern specified, NULL if either there is no match or, the string or the pattern is NULL. Here, a pattern is defined as an extended regular expression or just an ordinary string.
Syntax
以下是 MySQL regexp_substr() 函数的语法 -
Following is the syntax of the MySQL regexp_substr() function −
REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]])Parameters
regexp_substr() 函数采用以下参数值 -
The regexp_substr() function takes following parameter values −
-
expr: The string in which search is performed
-
pattern: The pattern that is searched in the string
此方法还接受以下可选参数：
This method also accepts following optional arguments −
-
pos: Starting position of the search
-
occurrence: Which occurrence of a match to replace. If omitted, the default is 1 so it retrieves the first occurrence only.
-
match_type: A string that specifies how to perform matching.
Example
以下示例显示了使用 SELECT 语句在简单字符串“Welcome To Tutorialspoint！”上使用 MySQL regexp_substr() 函数：
Following example shows the usage of MySQL regexp_substr() function on a simple string 'Welcome To Tutorialspoint!' using the SELECT statement as follows −
SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
如果字符串中不存在模式，则结果返回为 NULL。让我们尝试使用以下查询在同一字符串“Welcome To Tutorialspoint！”中搜索模式“Hi” -
If the pattern is not present in the string, the result is returned as NULL. Let us try to search for the pattern 'Hi' in the same string 'Welcome To Tutorialspoint!' using the following query −
SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'Hi') AS RESULT;输出如下：
Following is the output −
Example
让我们将 5 作为“pos”参数的值，以便从给定字符串中的第 5 个位置开始搜索；并且由于我们将出现值传递为 2，因此将检索模式“to”在第 5 个位置之后的第二次出现，而不管其大小写如何 -
Let us pass 5 as value to the 'pos' parameter so the search starts from the 5th position in the given string; and as we are passing the occurrence value as 2, the second occurrence of the pattern 'to' after 5th position will be retrieved, irrespective of its case −
SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'To', 5, 2, 'i')
AS RESULT;Example
如果传递给此函数的前两个参数中的任何一个为 NULL，则此函数返回 NULL。在下面的查询中，我们将 NULL 传递给表达式参数。
If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the expression parameter.
SELECT REGEXP_SUBSTR(NULL, 'value');执行给定的程序后，输出如下所示：
On executing the given program, the output is displayed as follows −
在此，我们将 NULL 作为要搜索的模式传递 -
Here, we are passing NULL as a pattern to search −
SELECT REGEXP_SUBSTR('Welcome to Tutorialspoint', NULL)
AS Result;输出如下：
Following is the output −
Example
在以下查询中，我们使用 REGEXP_SUBSTR() 函数对名为 CUSTOMERS 的数据库表执行搜索操作。首先，让我们使用以下查询创建表 -
In the following query, we are performing a search operation on a database table named CUSTOMERS using the REGEXP_SUBSTR() function. Firstly, let us create the table using the query below −
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 条记录添加到上述创建的表中 -
Following query adds 7 records into the above-created table −
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 表的所有记录 -
Execute the following query to display all the records of CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，我们使用 REGEXP_SUBSTR() 函数从以“Ra”开头的 NAME 列值中检索子字符串。
Now, we are using the REGEXP_SUBSTR() function to retrieve the substring from the the NAME column values that begin with 'Ra'.
SELECT REGEXP_SUBSTR(NAME, '^Ra') AS RESULT FROM CUSTOMERS;REGEXP_SUBSTR() Funcion Using a Client Program
我们还可以使用客户端程序执行 MySQL REGEXP_SUBSTR() 函数以从一组数据中检测指定模式。
We can also perform the MySQL REGEXP_SUBSTR() function using the client programs to detect specified patterns from a set of data.