alexa
Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry

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

109 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