Monday, April 11, 2011

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

No comments: