Converting a single row into multiple rows

Lets see how a single row can be converted into multiple rows based on some delimiter.

Example:

Assume there is row cell with value like below in a table or static text

Row1: "First;Second;Third;Fourth"

It can be converted into multiple rows like below using REGEXP_SUBSTR function.

Row1:  First
Row2: Second
Row3: Third
Row4: Fourth


WITH TAB_CONV
AS
(SELECT COL1 FROM TABLE1)
SELECT REGEXP_SUBSTR(COL1,'[^;]+',1,LEVEL) SPLIT_COL1 FROM TAB_CONV
CONNECT BY LEVEL <=(SELECT REGEXP_COUNT(COL1,';')+1 FROM TAB_CONV);

OR if using static text or text variable

SELECT REGEXP_SUBSTR('First;Second;Third;Fourth','[^;]+',1,LEVEL) SPLIT_COL1
FROM dual
CONNECT BY LEVEL <=(SELECT REGEXP_COUNT('First;Second;Third;Fourth',';')+1 FROM dual);

Note: here based on semicolon delimiter row text splitted.

Use case: This kind of split might be useful when you want to deal with Application Roles in OBIEE.

WITH T AS (SELECT 'VALUEOF(NQ_SESSION.ROLES)' APP_ROLES FROM DUAL)
SELECT UPPER(REGEXP_SUBSTR(APP_ROLES,'[^;]+',1,LEVEL)) SPLIT_APP_ROLES FROM T
CONNECT BY LEVEL <=(SELECT REGEXP_COUNT(APP_ROLES,';')+1 FROM T)

Because NQ_SESSION.ROLES variable will hold the value with semicolon separated. In order to use these values in any filter you must split into rows and apply filter on this splitted row column.