How to split a string field in several rows in Oracle Data Integrator 12c

Question

I am a newbie in ODI 12c. I have recently installed it and had several tests on it. My problem is that I have a table like this:

I want to split RULES column in different rows like this in ODI 12c:

Would you please guide me how I can do that in ODI 12c?

Any help is really appreciated.

Answer 1

REGEXP_SUBSTR() cannot be directly used within ODI (except can be used within a DB view) while REGEXP_REPLACE() can be for version 12. But it would be easier to operate through use of REGEXP_SUBSTR() function in order to split if it could be used. But the other function is harder to use for such splitting purposes.

Alternatively, you can use standard former functions such as SUBSTR(), INSTR() and LENGTH() along with a Hierarchical Query in order to generate rows as per split such as :

 SELECT timestamp,
        CASE WHEN level = 1 
             THEN SUBSTR( rules, 1, INSTR(rules,',') - 1 )
             WHEN level = LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
             THEN SUBSTR( rules, INSTR(rules,',',1,level-1)+1,
                                 LENGTH(rules)-INSTR(rules,',',1,level-1)+1)
             ELSE SUBSTR( rules, INSTR(rules,',',1,level-1)+1,  
                                 INSTR(rules,',',1,level)-INSTR(rules,',',1,level-1)-1)
              END AS rules
   FROM t
CONNECT BY level <= LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR timestamp = timestamp 

Demo