Oracle SQL - Extract date in filename-CodePudding

I have differents files, which are named this way:

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

How can I retrieve only the date of each file in Oracle SQL?

thank you in advance for your help

CodePudding user response：

Try this:

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;

CodePudding user response：

The logic seems to bring the square bracketed part if exists, otherwise the digits seperated by a dot per each, depending on the presented sample data. So consider using the following query with conditional

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

Demo