Oracle中移除指定字符串：保留邮件主题及正文内容

需求说明 #

• 现有表包含Caseid和Text两列，Text列是多封邮件的拼接内容
• 需要移除每封邮件中从'FROM:'到'SUBJECT:'前的所有内容，仅保留邮件主题及正文部分

测试数据 #

期望输出 #

尝试的SQL及问题 #

用户尝试的SQL语句：

with test as
(
 select 'FROM: ABHI RATHI <(><<)>ABHIRATHI@XYZ.COM> SENT: FRIDAY, JUNE 17, 2022 12:33 PM TO: VR, SARATH <(><<)>SARATH.VR@ABC.COM>; SUBJECT: RE: LOG # 20092017656 : [ SECURITY TEAM IDENTIFY THERE IS SUSPICION ACTIVITY ] [ ACTIONS REQUIRED ] FROM: YUAN LN <(><<)>YIQUANLIN@XYZ.COM> SENT: FRIDAY, JUNE 10, 2022 3:40 PM TO: VR, SARATH <(><<)>SARTH.VIR@ABC.COM> SINGH, ROHINI <(><<)>ROSHINI.SINGH@PQR.COM>SUBJECT: RE: LOG # 20092017656 : HI SARTH SORRY FOR THE LATE REPLY AS I AM ON LEAVE TODAY CAN WE HAVE A CALL NEXT MONDAY? THANKS AND REGARDS YUAN' as text from dual
 )
select regexp_replace(text, 'FROM:[[:print:]]*[\.][a-zA-Z]{2,4}[\>]','' )
from test

执行后丢失了第一封邮件的内容，仅保留了第二封的主题和正文。

正确解决方案 #

原正则使用贪婪匹配导致过度截取，调整为精准匹配FROM:到下一个SUBJECT:前的内容，采用非贪婪模式避免跨邮件截取：

with test as
(
 select 'FROM: ABHI RATHI <(><<)>ABHIRATHI@XYZ.COM> SENT: FRIDAY, JUNE 17, 2022 12:33 PM TO: VR, SARATH <(><<)>SARATH.VR@ABC.COM>; SUBJECT: RE: LOG # 20092017656 : [ SECURITY TEAM IDENTIFY THERE IS SUSPICION ACTIVITY ] [ ACTIONS REQUIRED ] FROM: YUAN LN <(><<)>YIQUANLIN@XYZ.COM> SENT: FRIDAY, JUNE 10, 2022 3:40 PM TO: VR, SARATH <(><<)>SARTH.VIR@ABC.COM> SINGH, ROHINI <(><<)>ROSHINI.SINGH@PQR.COM>SUBJECT: RE: LOG # 20092017656 : HI SARTH SORRY FOR THE LATE REPLY AS I AM ON LEAVE TODAY CAN WE HAVE A CALL NEXT MONDAY? THANKS AND REGARDS YUAN' as text from dual
 )
select regexp_replace(text, 'FROM:.*?(?=SUBJECT:)','', 1, 0, 'n') as cleaned_text
from test

正则说明 #

• FROM:：匹配邮件起始标识
• .*?：非贪婪模式匹配任意字符，防止跨邮件截取
• (?=SUBJECT:)：正向预查，匹配到下一个SUBJECT:时停止，不会包含SUBJECT:本身
• 1, 0, 'n'：1表示从第一个字符开始替换，0表示替换所有匹配项，'n'开启多行模式确保每封邮件的FROM:都被正确识别

执行后即可得到期望输出，保留所有邮件的主题及正文内容。

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