MySQL 正则表达式:regexp_instr 函数
在 MySQL 中, REGEXP_INSTR()
函数返回与正则表达式模式匹配的子字符串的起始索引。
索引从 1
开始。如果不匹配,则返回 0
。
语法
语法如下:
REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
expr
为源字符串,pat
为正则表达式。
pos
为可选参数,标识开始匹配的位置,默认为1
。
occurrence
为可选参数,标识匹配的次数,默认为 1
。
return_option
为可选参数,指定返回值的类型。如果为 0
,则返回匹配的第一个字符的位置。如果为 1
,则返回匹配的最后一个位置,默认为 0
。
match_type
为可选参数,允许优化正则表达式。例如,可以使用此参数指定是否区分大小写。
示例 1 – 基本用法
基本示例:
SELECT REGEXP_INSTR('Cat', 'at') Result;
结果:
+--------+
| Result |
+--------+
| 2 |
+--------+
子字符串从位置 2 开始存在匹配项。
示例 2 – 不匹配
不匹配的示例:
SELECT REGEXP_INSTR('Cat', '^at') Result;
结果:
+--------+
| Result |
+--------+
| 0 |
+--------+
没有匹配项,因为指定字符串必须从子字符串开始,结果返回 0
。
让我们把它改为 该子字符串开头 :
SELECT REGEXP_INSTR('at', '^at') Result;
结果:
+--------+
| Result |
+--------+
| 1 |
+--------+
示例 3 – 参数 pos
指定开始位置:
SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) Result;
结果:
+--------+
| Result |
+--------+
| 5 |
+--------+
我们得到了第二次出现的索引。
请注意,无论在哪里指定起始位置,索引都将从位置 1
开始计数。
以下示例更加清楚地说明了这一点:
SELECT
REGEXP_INSTR('Cat Cat', 'Cat', 2) AS 'Pos 2',
REGEXP_INSTR('Cat Cat', 'Cat', 3) AS 'Pos 3',
REGEXP_INSTR('Cat Cat', 'Cat', 5) AS 'Pos 5';
结果:
+-------+-------+-------+
| Pos 2 | Pos 3 | Pos 5 |
+-------+-------+-------+
| 5 | 5 | 5 |
+-------+-------+-------+
当然,根据您的正则表达式模式,可以返回完全不同的子字符串的索引。例:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';
结果:
+-------+-------+-------+
| Pos 1 | Pos 2 | Pos 6 |
+-------+-------+-------+
| 1 | 5 | 16 |
+-------+-------+-------+
我们可以使用 REGEXP_SUBSTR()
函数检查子字符串:
SELECT
REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',
REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',
REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';
结果:
+-------+-------+-------+
| Pos 1 | Pos 2 | Pos 6 |
+-------+-------+-------+
| Cat | Cit | Cut |
+-------+-------+-------+
示例 4 – 参数 occurrence
这是使用参数 occurrence
的示例。在所有情况下,我们都从位置 1
开始:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1) 'Occurrence 1',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2) 'Occurrence 2',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3) 'Occurrence 3';
结果:
+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
| 1 | 5 | 16 |
+--------------+--------------+--------------+
但是,如果我们从不同位置开始,结果将会有所不同:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 1) 'Occurrence 1',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 2) 'Occurrence 2',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 3) 'Occurrence 3';
结果:
+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
| 5 | 16 | 0 |
+--------------+--------------+--------------+
发生这种情况是因为我们的起始位置是在第一次出现之后开始的。因此,2
次匹配变成 1
次匹配的结果,3
次匹配变成 2
次匹配的结果。并且由于不存在更多的次数,因此 3
次匹配的结果为零(即没有达到 3
次匹配)。
示例 5 – 参数 return_option
以下是使用参数 return_option
的示例:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Option 0',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1) 'Option 1';
结果:
+----------+----------+
| Option 0 | Option 1 |
+----------+----------+
| 1 | 4 |
+----------+----------+
Option 0
返回了匹配的第一个字符的位置。Option 1
返回了匹配之后的位置。
如果将其应用于上一个示例,如下:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Occurrence 1',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 0) 'Occurrence 2',
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 0) 'Occurrence 3'
UNION ALL
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1),
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 1),
REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 1);
结果:
+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
| 1 | 5 | 16 |
| 4 | 8 | 19 |
+--------------+--------------+--------------+
在这种情况下,我们使用 Option 0
设置了一组,使用 Option 1
设置了一组,然后使用 UNION ALL
将它们连接在一起。
示例 6 – 参数 match_type
您可以使用其他参数来确定匹配类型。可以指定诸如匹配是否区分大小写,是否包括行终止符之类的内容。
这是指定区分大小写的匹配和不区分大小写的匹配的示例:
SELECT
REGEXP_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, 'c') 'Case-Sensitive',
REGEXP_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, 'i') 'Case-Insensitive';
结果:
+----------------+------------------+
| Case-Sensitive | Case-Insensitive |
+----------------+------------------+
| 0 | 1 |
+----------------+------------------+
参数 match_type
可以包含以下字符:
c
匹配区分大小写。
i
匹配不区分大小写。
m
多行模式。识别字符串中的行终止符。默认是仅在字符串表达式的开头和结尾匹配行终止符。
n
与 .
行终止符匹配。
u
仅匹配 Unix
的行结尾。只有换行符被识别为以 .
, ^
和 $
结尾的行。