PLSQL正则提取含同分隔符的DATES OF SERVICE字段子串

需要从结构化字符串中提取DATES OF SERVICE对应的字段值，核心难点：

• 字段值内部可能包含0个或多个与外层字段分隔符相同的/
• 后续字段名称不固定，无法用固定字段名作为结束标识

示例输入字符串 #

Text1 = 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: UNCLEAR /TOOTH HISTORY GIVEN: YES 
/BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A '

Text2 = 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: 12/22/2021 /TOOTH HISTORY GIVEN: YES 
/BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A '

Text3 = 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: 12/22/2021, 3/5/2022 /TOOTH HISTORY 
GIVEN: YES /BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A '

期望提取结果 #

Text1_result = 'UNCLEAR'
Text2_result = '12/22/2021'
Text3_result = '12/22/2021, 3/5/2022'

解决方案 #

利用后续字段的格式特征确定截断点：所有字段均以/开头，后跟大写字母开头的字段名（如/TOOTH HISTORY、/BENEFITS NEEDED）。通过正则匹配DATES OF SERVICE:之后的内容，直到遇到下一个符合/[A-Z]格式的分隔符或字符串结尾。

单个字符串提取示例 #

SELECT 
  REGEXP_SUBSTR(
    Text1,
    'DATES OF SERVICE:\s*(.*?)(?=\s*/[A-Z]|$)',
    1, 1, 'i', 1
  ) AS dates_of_service
FROM dual;

参数说明 #

• 'DATES OF SERVICE:\s*(.*?)(?=\s*/[A-Z]|$)'：正则表达式
  • DATES OF SERVICE:\s*：匹配字段名，忽略后续空白字符
  • (.*?)：非贪婪模式匹配字段值，避免过度匹配到后续字段
  • (?=\s*/[A-Z]|$)：正向预查，匹配到下一个/后跟大写字母的位置，或字符串结尾
• 'i'：忽略大小写匹配（可根据需求调整）
• 最后一个1：返回第一个捕获组的内容（即目标字段值）

批量处理示例代码 #

WITH sample_texts AS (
  SELECT 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: UNCLEAR /TOOTH HISTORY GIVEN: YES 
/BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A ' AS text FROM dual
  UNION ALL
  SELECT 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: 12/22/2021 /TOOTH HISTORY GIVEN: YES 
/BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A ' FROM dual
  UNION ALL
  SELECT 'CALLBACK NUMBER: 999-999-9999 /SEGMENT: DENTAL. ASSOCIATE RESOLUTION:  
 /PARTICIPATION: PAR PER PROVIDER /DATES OF SERVICE: 12/22/2021, 3/5/2022 /TOOTH HISTORY 
GIVEN: YES /BENEFITS NEEDED: N/A/CLAIM NUMBER: N/A /TRANSFERRED: N/A ' FROM dual
)
SELECT 
  REGEXP_SUBSTR(
    text,
    'DATES OF SERVICE:\s*(.*?)(?=\s*/[A-Z]|$)',
    1, 1, 'i', 1
  ) AS dates_of_service_result
FROM sample_texts;

执行以上代码可得到符合预期的结果，能正确处理字段值内部包含/的场景，且不受后续字段名称变化的影响。

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