Oracle SQL -在文件名中提取日期

Question

我有不同的文件，它们是这样命名的:

Input                                     Desired Output
ZMMFI 2021.12.P1.xlsx                 ->  2021.12 
ZMMFI 2021.12.P2.xlsx                 ->  2021.12
ZMMFI 2021.12.P3.xlsx                 ->  2021.12
ZMMFI 2021.12.P4.xlsx                 ->  2021.12
ZMMFI_OESV 2018.12 [08.01.2019].xlsx  ->  08.01.2019
ZMMFI_OESV 2022.02.xlsx               ->  2022.02
ZMMFI_OESX 2020.12 [27.01.2021].xlsx  ->  27.01.2021
ZMMFI_OSFI 2018.10 [08.01.2019].xlsx  ->  08.01.2019
ZMMFI_OSFI 2022.02.xlsx               ->  2022.02
ZMMFI_RTMV 2018.02 [08.01.2019].xlsx  ->  08.01.2019 
ZMMFI_RTMV 2018.04 [08.01.2019].XLSX  ->  08.01.2019
ZMMFI_RTMV 2018.06 [08.01.2019].XLSX  ->  08.01.2019
ZMMFI_RTMV 2018.08 [08.01.2019].XLSX  ->  08.01.2019
ZMMFI_RTMV 2018.10 [08.01.2019].XLSX  ->  08.01.2019
ZMMFI_RTMV 2018.12 [08.01.2019].xlsx  ->  08.01.2019
ZMMFI_RTMV 2022.02.xlsx               ->  2022.02

如何在Oracle SQL中检索每个文件的日期?

提前感谢您的帮助

Answer 1

试试这个:

WITH file_names AS 
(
  SELECT 'ZMMFI 2021.12.P1.xlsx'  AS title      FROM dual UNION ALL
  SELECT 'ZMMFI 2021.12.P2.xlsx'                FROM dual UNION ALL
  SELECT 'ZMMFI 2021.12.P3.xlsx'                FROM dual UNION ALL
  SELECT 'ZMMFI 2021.12.P4.xlsx'                FROM dual UNION ALL
  SELECT 'ZMMFI_OESV 2018.12 [08.01.2019].xlsx' FROM dual UNION ALL
  SELECT 'ZMMFI_OESV 2022.02.xlsx'              FROM dual UNION ALL
  SELECT 'ZMMFI_OESX 2020.12 [27.01.2021].xlsx' FROM dual UNION ALL
  SELECT 'ZMMFI_OSFI 2018.10 [08.01.2019].xlsx' FROM dual UNION ALL
  SELECT 'ZMMFI_OSFI 2022.02.xlsx'              FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.02 [08.01.2019].xlsx' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.04 [08.01.2019].XLSX' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.06 [08.01.2019].XLSX' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.08 [08.01.2019].XLSX' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.10 [08.01.2019].XLSX' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2018.12 [08.01.2019].xlsx' FROM dual UNION ALL
  SELECT 'ZMMFI_RTMV 2022.02.xlsx'              FROM dual
)
SELECT TRIM(NVL(REGEXP_SUBSTR(title, '[(.+)]', 1, 1, NULL, 1), REGEXP_SUBSTR(title, '( w+...)', 1, 1, NULL, 1)))
  FROM file_names;

Answer 2

如果存在，逻辑似乎会带来方括号部分，否则每个数字由一个点分隔，这取决于所呈现的示例数据。因此，考虑对条件

使用以下查询

SELECT CASE WHEN INSTR(file_name,'[') >0 THEN
                 REGEXP_SUBSTR(file_name, '[(.+)]', 1, 1, NULL, 1) 
            ELSE
                 REGEXP_SUBSTR(file_name, 'd+.d+')
             END     
  FROM t