Extract date and name in Oracle SQL ?

• Yogesh Kumar
• 04-May-2022
• 0

You can do it using (fast) string functions (rather than slow regular expressions):

 SELECT SUBSTR(value, 1, INSTR(value, '_', -1)) AS file_name,
       TO_DATE(
         SUBSTR(value, INSTR(value, '_', -1) + 1, 8),
         'YYYYMMDD'
       ) AS dt
FROM   table_name 

If you do want to use (slower) regular expressions then you can use the same pattern ^(.*_)(\d{8}).csv$ for both parts and extract the different capturing groups:

 SELECT REGEXP_SUBSTR(value, '^(.*_)(\d{8}).csv$', 1, 1, NULL, 1) AS file_name,
       TO_DATE(
         REGEXP_SUBSTR(value, '^(.*_)(\d{8}).csv$', 1, 1, NULL, 2),
         'YYYYMMDD'
       ) AS dt
FROM   table_name 

Which, for the sample data:

 CREATE TABLE table_name (value) AS
SELECT 'TRG_jira_creditentity_20220426.csv'     FROM DUAL UNION ALL
SELECT 'TRG_jira_CRL_Boni_20220426.csv'         FROM DUAL UNION ALL
SELECT 'TRG_jira_CRL_Branchen_20220426.csv'     FROM DUAL UNION ALL
SELECT 'TRG_jira_CRL_Counterparty_20220426.csv' FROM DUAL UNION ALL
SELECT 'TRG_jira_CRL_KNE_20220426.csv'          FROM DUAL UNION ALL
SELECT 'TRG_jira_CRL_Länder_20220426.csv'       FROM DUAL; 

Both output:

FILE_NAME	DT
TRG_jira_creditentity_	2022-04-26 00:00:00
TRG_jira_CRL_Boni_	2022-04-26 00:00:00
TRG_jira_CRL_Branchen_	2022-04-26 00:00:00
TRG_jira_CRL_Counterparty_	2022-04-26 00:00:00
TRG_jira_CRL_KNE_	2022-04-26 00:00:00
TRG_jira_CRL_Länder_	2022-04-26 00:00:00

* Be the first to Make Comment