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

如果存在，逻辑似乎会带来正方形的零件，否则，根据所提出的样本数据，则每个DOT分类的数字分开。因此，请考虑使用以下查询与条件

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