Basic SQLs – RegEx Regular Expression – SQL Split String

 

Business Need:

Oracle Database SQL, SQL PLUS

Split a sting into multiple parts – name, EDI Inputs, Comma Separated Values, Character delimiters, etc.

 

Tables:

Any …

 

SQLs:

Split based on “_”:

SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 1)) FROM DUAL;        -- 4811             - Order Number / Receipt Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 2)) FROM DUAL;        -- ONT / AR - application name
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 3)) FROM DUAL;        -- 127044    - Payment Order Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 4)) FROM DUAL;        -- 5798526   - Order Header ID
 

“_” can be replaced with COMMA, SPACE, HYPEN, TAB or any other character to split based on the given character.

  

Eg: Consider a name string with “<First> <Middle><Last>” format. Here is the query to get First, Middle and Last names:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ,]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ,]+', 1, 3) as middle 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL, SQLPLUS, regexp_substr, RegEx

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