alexa

Extract date and name in Oracle SQL ?

Extract date and name in Oracle SQL ?

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

402 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry