如何使用regexp_substr()提取字符串中特定单词后的数字

针对你给出的字符串格式，要提取serial后的数字，下面是不同主流数据库对应的SQL写法：

Oracle #

直接利用REGEXP_SUBSTR的捕获组提取功能：

SELECT REGEXP_SUBSTR('top-level:volume(1):semifinished(21491628):serial(21441769)', 'serial\((\d+)\)', 1, 1, NULL, 1) AS serial_number
FROM dual;

• 正则说明：serial\((\d+)\)精准匹配serial(数字)的结构，(\d+)把数字部分标记为捕获组；最后一个参数1指定提取第1个捕获组的内容。

PostgreSQL #

可以用正向预查定位数字位置，或者提取捕获组：

方法1：正向预查 #

SELECT REGEXP_SUBSTR('top-level:volume(1):semifinished(21491628):serial(21441769)', '(?<=serial\()\d+') AS serial_number;

• 正则说明：(?<=serial\()确保匹配的数字前面是serial(，\d+匹配所有连续数字。

方法2：捕获组提取 #

SELECT (REGEXP_MATCHES('top-level:volume(1):semifinished(21491628):serial(21441769)', 'serial\((\d+)\)'))[1] AS serial_number;

MySQL 8.0+ #

MySQL 8.0及以上版本支持REGEXP_SUBSTR，注意转义符要用双反斜杠：

SELECT REGEXP_SUBSTR('top-level:volume(1):semifinished(21491628):serial(21441769)', '(?<=serial\\()\\d+') AS serial_number;

如果是从表中查询，把示例里的字符串替换成你的列名即可，比如Oracle环境下查询表your_table的target_col列：

SELECT REGEXP_SUBSTR(target_col, 'serial\((\d+)\)', 1, 1, NULL, 1) AS serial_number
FROM your_table;

内容的提问来源于stack exchange，提问作者Drake