Search code examples
sqloracle-databasesubstringregex-replace

Extract date and name in Oracle sql

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_'.

Solution

  • 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