Mysql 简明教程
MySQL - REGEXP_INSTR() Function
MySQL 支持多种模式匹配操作来从庞大的数据库表中检索筛选后的结果集。但是，使用正则表达式进行模式匹配是执行复杂搜索的强大方式。
MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.
正则表达式在技术上定义为代表输入文本中模式的一系列字符。它用于使用某些模式查找或替换文本字符串；该模式可以是单个字符、多个字符或单词等。
A regular expression is technically defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.
MySQL 中的这些正则表达式提供了各种函数和操作符，以便轻松执行搜索操作。这样一个功能是 regexp_instr() 功能。
These regular expressions in MySQL provide various functions and operators to easily perform the search operations. One such function is regexp_instr() function.
MySQL REGEXP_INSTR() Function
MySQL regexp_instr() 函数用于匹配指定模式与字符串或数据库表中的数据。此函数返回与指定模式匹配的字符串子字符串的起始索引，如果没有匹配项则返回 0，如果字符串或模式为 NULL 则返回 NULL。此字符串的字符索引从 1 开始。
The MySQL regexp_instr() function is used to match specified patterns with either a string or the data in database tables. This function returns the starting index of the substring of a string that matches the specified pattern, returns 0 if there is no match, or NULL if the string or the pattern is NULL. Character indices of this string starts at 1.
Syntax
以下是 MySQL regexp_instr() 函数的语法 -
Following is the syntax of the MySQL regexp_instr() function −
REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])其中 expr 是要执行搜索的字符串，pat 是要搜索的模式/正则表达式。除了表达式和字符串值外，此方法还接受以下可选参数。
Where expr is the string in which the search is to be performed and pat is the pattern/regular expression that is to be searched. In addition to the expression and string values this method accepts the following optional parameters.
Parameters
regexp_instr() 函数采用以下参数值 -
The regexp_instr() function takes following parameter values −
-
expr: The string in which search is performed
-
pattern: The pattern that is searched in the string
以下是可传给此函数的可选参数 -
Following are the optional arguments that can be passed to this function −
-
pos: The position in expr at which to start the search. If omitted, the default is 1.
-
occurrence: Which occurrence of a match to search for. If omitted, the default is 1.
-
return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring’s first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.
-
*match_type:*This is a string which consists of various characters representing the desired features of the match this may contain one or all of these characters. Following are various characters using which you can specify the match type.
Example
在此示例中，我们使用 MySQL REGEXP_INSTR() 函数对一个简单字符串执行搜索操作 −
In this example, we are performing a search operation on a simple string using the MySQL REGEXP_INSTR() function −
SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT;在第 9 个索引处找到了模式“To” −
The pattern 'To' is found at 9th index −
如果没有在字符串中找到匹配项，则返回值为“0” −
If there is no match found in the string, the return value will be '0' −
SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'xx') AS RESULT;输出如下：
Following is the output −
Example
我们还可以将 optional arguments 传递给此函数，并观察结果。此处，搜索开始位置从 5 开始，查找在该位置后的第 2 个出现的“T”。由于返回值选项设置为 1，因此返回匹配项之后的位置。 −
Let us also pass optional arguments to this function and observe the result. Here, the search search position starts at 5 to find the 2nd occurrence of 'T' after that position. As the return option is set to 1, the position following the match is returned. −
SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'T', 5, 2, 1) AS RESULT;Example
以下查询在提供的字符串“9848032919”中搜索任意字母字符的位置。如果找到，则返回 1。否则返回 0。
The following query searches for the position for any alphabetic character in the provided string '9848032919'. If found, it returns 1. Else, 0.
SELECT REGEXP_INSTR('9848032919', '[[:alpha:]]');Example
以下查询在提供的字符串中搜索“town”或“city”的位置 −
The below query searches for the position of either 'town' or 'city' in the provided string −
SELECT REGEXP_INSTR('Vishakapatnam is city of destiny ', 'town|city')
As Result;Example
如果传递给此函数的前两个参数中的任何一个是 NULL，则此函数会返回 NULL。此处，我们将“NULL”作为搜索模式传递。
If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing 'NULL' as search pattern.
SELECT REGEXP_INSTR('Tutorialspoint', NULL)
As Result;如果我们编译并运行查询，则结果会生成如下 −
If we compile and run the query, the result is produced as follows −
在以下查询中，我们正在向字符串参数传递“NULL”。
In the following query, we are passing 'NULL' to the string parameter.
SELECT REGEXP_INSTR(NULL, 'to')
As Result;当我们执行以上查询时，输出如下 −
When we execute the query above, the output is obtained as follows −
Example
在另一个示例中，让我们使用 REGEXP_INSTR() 函数对名为 CUSTOMERS 的数据库表执行搜索操作。首先，让我们使用以下查询创建表 −
In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_INSTR() function. Firstly, let us create the table using the following query −
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)
);使用以下 INSERT 查询在上述已创建的表中插入一些记录 −
Insert some records into the above created table using the following INSERT query −
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 below query to display all the records present in CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
以下查询选择从 CUSTOMERS 表中的 NAME 列中以字母“K”开头的字符串的第一个出现的位置 −
The following query selects the position of the first occurrence of a string that starts with the letter 'K' from the NAME column in the CUSTOMERS table −
SELECT REGEXP_INSTR(NAME, '^K')
AS RESULT FROM CUSTOMERS;如我们在以下输出中看到的，NAME 列中有三个以字母 K 开头的字符串。
As we can see in the output below, there are three string in NAME column that starts with letter K.
Client Program
我们还可以使用客户端程序（例如 PHP、Node.js、Java、Python）执行 MySQL REGEXP_INSTR() 函数，以将指定模式与字符串或数据库表中的数据进行匹配。
We can also perform the MySQL REGEXP_INSTR() function using the client programs (such as PHP, Node.js, Java, Python) to match specified pattern with either a string or the data in database tables.