I have prg_tbl table in which i insert filenames from directory in the column Filename and values are stored in column Filename as :

TRG_jira_creditentity_20220426.csv
TRG_jira_CRL_Boni_20220426.csv
TRG_jira_CRL_Branchen_20220426.csv
TRG_jira_CRL_Counterparty_20220426.csv
TRG_jira_CRL_KNE_20220426.csv
TRG_jira_CRL_Länder_20220426.csv

In one select query i want to extract date from this rows for example '20220426'. And in another select query i want to extract filename for example 'TRG_jira_creditentity_'.

I am not sure how to do this using select query as the dates from filename gets changes but the prefix which is part of filename it remains static for example 'TRG_jira_creditentity_'.

CodePudding user response：

One option is to use REGEXP_REPLACE() function such as

SELECT REGEXP_REPLACE(filename,'^(.*_)([^.]*).*','\2') AS col1,
       REGEXP_REPLACE(filename,'[^_] $') AS col2       
  FROM prg_tbl

Demo

CodePudding user response：

You want to use regular expressions (regexes). For example:

SELECT REGEXP_SUBSTR( 'TRG_jira_creditentity_20220426.csv',
    'TRG_jira_creditentity_(\d )\.csv', 1, 1, NULL, 1 ) from dual;

returns

20220426

Here we search for the pattern \d which means "one or more digits" after the string TRG_jira_creditentity_.

There are many tutorials on regexes out there, but note that all regex implementations are slightly different, and Oracle especially. Probably the best place to start would be the Oracle docs themselves, such as here, depending on what version of Oracle you're running.

CodePudding user response：

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

db<>fiddle here