RegEx / Regular Expression: Folder Structure: Get File Name

 

Problem:

You have a list of file names in different directories. You need to get just the file name, stripping directory names.

Here is an example input:

/appsR122/ABCDEV/fs1/FMW_Home/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web/EBS_web.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/oacore_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/forms_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/oacore_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/forms_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer-diagnostic.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server1/logs/oafm_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/aferror.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/appl/rgf/javacache.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/10890.dispatcher.log

 

Expected Output:

Just names of the files:

EBS_web.log
access.log
oacore_server1.log
access.log
forms_server1.log
access.log
oacore_server2.log
access.log
forms_server2.log
AdminServer.log
AdminServer-diagnostic.log
oafm_server1.log
aferror.log
javacache.log
10890.dispatcher.log
 

 

Query:

Use Regular Expression to strip out first part and keep only delimiter:

SELECT REGEXP_SUBSTR(<Input String>, '[^/]+$', 1, 1)
FROM DUAL;
 

Eg:

SELECT REGEXP_SUBSTR('/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log', '[^/]+$', 1, 1)
FROM DUAL;
 

This can be used to last part of any string, separated by a delimiter. Just substitute the second parameter ('[^/]+$') for the new delimiter.

 

 

Keywords:

RegEx, Regular Expression, REGEXP_SUBSTR, String Function, PL/SQL, Query, SQL, SQLPLUS