CREATE TABLE TEST1(COL1,COL2,COL3) AS
(
SELECT 1,'123;567', 'G' FROM DUAL
)
SELECT COL1,SUBSTR(COL2,1,INSTR(COL2,';')-1) AS SPLIT_FROW,COL3,SUBSTR(COL2,INSTR(COL2,';')+1) AS SPLIT_SROW FROM TEST1
Actual Result:
COL1 SPLIT_FROW COL3 SPLIT_SROW
1 123 G 567
Expected result:
COL1 SPLIT_FROW COL3
1 123 G
1 567 G