Filename validation Using Regular Expression

Extract the filename from a full file path
select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) FROM dba_data_files;

select substr(file_name,(instr(file_name,'\',-1,1)+1),length(file_name)) FROM dba_data_files;
Validates a long filename using Windows' rules:
select file_name from table_of_files WHERE not REGEXP_LIKE(file_name,'^[^\\\./:\*\?\"<>\|]{1}[^\\/:\*\?\"<>\|]{0,254}$');

combinning these two SQLs:
WITH files AS (
select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) base_filename FROM dba_data_files)
select base_filename from files WHERE REGEXP_LIKE(base_filename,'^[^\\\./:\*\?\"<>\|]{1}[^\\/:\*\?\"<>\|]{0,254}$');


Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

List RMAN backups size

Oracle 18c - New Features for Active Data Guard