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