如何在Oracle存储过程中提取文本TO:字段后的邮箱地址

核心思路是借助Oracle内置的正则匹配函数和层级查询，无需自定义复杂的字符串切割逻辑，仅需单条查询即可完成所有TO:字段后邮箱地址的提取。

核心查询逻辑 #

假设存储过程的输入邮件文本参数为p_mail_text，提取所有收件人邮箱的查询语句如下：

SELECT TRIM(REGEXP_SUBSTR(p_mail_text, 'TO: (.*)$', 1, LEVEL, 'm', 1)) AS to_email
FROM dual
CONNECT BY REGEXP_SUBSTR(p_mail_text, 'TO: (.*)$', 1, LEVEL, 'm', 1) IS NOT NULL;

参数说明：

• 正则表达式TO: (.*)$：匹配以TO: 开头的行，捕获冒号后到行尾的所有内容
• 匹配参数'm'：开启多行匹配模式，让$匹配每一行的结尾，而非整个文本的结尾
• LEVEL配合CONNECT BY：遍历所有匹配到的TO:行，直到没有新的匹配项为止
• TRIM()：清理邮箱地址前后可能存在的多余空格

完整存储过程示例 #

如果需要将提取结果作为输出参数返回，可参考以下实现：

1. 先定义存储邮箱列表的集合类型

CREATE OR REPLACE TYPE email_list_t AS TABLE OF VARCHAR2(255);
/

2. 编写存储过程

CREATE OR REPLACE PROCEDURE extract_to_emails(
    p_mail_text IN CLOB,
    p_to_emails OUT email_list_t
) AS
BEGIN
    SELECT TRIM(REGEXP_SUBSTR(p_mail_text, 'TO: (.*)$', 1, LEVEL, 'm', 1))
    BULK COLLECT INTO p_to_emails
    FROM dual
    CONNECT BY REGEXP_SUBSTR(p_mail_text, 'TO: (.*)$', 1, LEVEL, 'm', 1) IS NOT NULL;
END extract_to_emails;
/

以上实现针对示例输入文本，会返回两行结果：john.doe@world.com、jane.doe@world.com，完全符合提取需求。

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