Filename validation Using Regular Expression

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

windows
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}$');

Comments

Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

Export in Pl/Sql via DBMS_DATAPUMP