CREATE TABLE table_1 (
e_id NUMBER(10),
text VARCHAR2(400)
);
insert into table_1 values(111,'101|ABC;102|PQR');
This is one table where value for text column is separated by | and ;. But I need to split this into the new records and expected output should be like below
+-------+---------+
| e_id | text |
+-------+---------+
| 111 | 101|ABC |
| 111 | 102|PQR |
+-------+---------+
My Attempt:
select e_id
,trim(regexp_substr(text, '[^;]+', 1, level)) end as split_text
from table_1
connect by e_id = prior e_id
and level <= regexp_count( text, ';') + 1
and prior sys_guid() is not null
But I am getting error like FROM keyword not found where expected