Oracle从指定字段提取排除CID/FEATURE的最小开始与最大结束日期

实现思路 #

• 先按分隔符->拆分path_start_date字段得到所有层级条目，过滤掉以CID、FEATURE开头的无效条目
• 从有效条目中提取末尾两个DD-MON-YY格式的日期，分别作为该条目的开始、结束日期
• 聚合所有有效日期得到最小开始日期、最大结束日期，回写至表中对应字段

实现代码 #

验证查询语句（可先执行确认提取结果是否符合预期） #

SELECT 
  MIN(TO_DATE(REGEXP_SUBSTR(trim(entry), '.*-([0-9]{2}-[A-Z]{3}-[0-9]{2})-([0-9]{2}-[A-Z]{3}-[0-9]{2})$', 1, 1, NULL, 1), 'DD-MON-RR')) AS min_start_date,
  MAX(TO_DATE(REGEXP_SUBSTR(trim(entry), '.*-([0-9]{2}-[A-Z]{3}-[0-9]{2})-([0-9]{2}-[A-Z]{3}-[0-9]{2})$', 1, 1, NULL, 2), 'DD-MON-RR')) AS max_end_date
FROM temp1,
     XMLTABLE(('"' || REPLACE(TRIM(BOTH ' -> ' FROM path_start_date), ' -> ', '","') || '"')) 
     COLUMNS entry VARCHAR2(1000) PATH '.'
WHERE trim(entry) NOT LIKE 'CID%' 
  AND trim(entry) NOT LIKE 'FEATURE%';

正式更新语句 #

UPDATE temp1 t
SET (start_date, end_date) = (
  SELECT 
    MIN(TO_DATE(REGEXP_SUBSTR(trim(entry), '.*-([0-9]{2}-[A-Z]{3}-[0-9]{2})-([0-9]{2}-[A-Z]{3}-[0-9]{2})$', 1, 1, NULL, 1), 'DD-MON-RR')),
    MAX(TO_DATE(REGEXP_SUBSTR(trim(entry), '.*-([0-9]{2}-[A-Z]{3}-[0-9]{2})-([0-9]{2}-[A-Z]{3}-[0-9]{2})$', 1, 1, NULL, 2), 'DD-MON-RR'))
  FROM XMLTABLE(('"' || REPLACE(TRIM(BOTH ' -> ' FROM t.path_start_date), ' -> ', '","') || '"')) 
       COLUMNS entry VARCHAR2(1000) PATH '.'
  WHERE trim(entry) NOT LIKE 'CID%' 
    AND trim(entry) NOT LIKE 'FEATURE%'
);
COMMIT;

结果验证 #

更新完成后执行查询即可看到结果：

SELECT 
  TO_CHAR(start_date, 'DD-MON-RR') AS Start_Date,
  TO_CHAR(end_date, 'DD-MON-RR') AS End_Date
FROM temp1;

输出和期望结果一致：

Start_Date = 13-NOV-18
End_Date   = 15-AUG-22

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