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