My table has data like the below CTE is generating. Here we have STRT and ENDSTRT in some lines. I want to find out the rows having STRT and STRTEND and want to assign a level to these.
As there are nested levels i want to have a additional column with nested level details some thing like i have shown in expected result.
WITH t (id, data) AS
(
SELECT 0, 'KmBeY STRT' FROM DUAL UNION
SELECT 1, 'SZL2PR2YDNAZNGM1SRZIK' FROM DUAL UNION
SELECT 2, 'GLOVEOADWRWJU4D4CBLRIX' FROM DUAL UNION
SELECT 3, 'j{fR.kc' FROM DUAL UNION
SELECT 4, '_MKD%Hq,SoojohC(Bg"O}A6THFT STRT' FROM DUAL UNION
SELECT 5, '3AFDZ' FROM DUAL UNION
SELECT 6, 'SZGUFLA ENDSTRT RFFVBHKN ' FROM DUAL UNION
SELECT 7, '|y)t ' FROM DUAL UNION
SELECT 8, 'STRT' FROM DUAL UNION
SELECT 9, 'rR' FROM DUAL UNION
SELECT 10, 'vrDyhnrwpRQOVoFtwyKNDDLc STRT NAENRGCnNiOVJ' FROM DUAL UNION
SELECT 11, '4UVX6U2T1U744LC46U58ZVVP2ZLOK1X0RU4GW51K477DXH5X3T' FROM DUAL UNION
SELECT 12, '%-P3oNXir!okc>U#hFa:$CZwB$' FROM DUAL UNION
SELECT 13, 'm)\1J4Y@d4i/sC' FROM DUAL UNION
SELECT 14, 'iHbtp3/1+=G2,D] STRT W_F$K$F$$jmx;%*W.#[8' FROM DUAL UNION
SELECT 15, '@~5C%CVX8xD[nZwisWU6[0v=g?.x`){d]m#LH"\_}HG\T3o}0c9' FROM DUAL UNION
SELECT 16, 'T53BDAQHJBWQF39NKFU0ZV6DQAX1F' FROM DUAL UNION
SELECT 17, 'W4SZ7C7WKYKP END STRT' FROM DUAL UNION
SELECT 18, 't3h:o1a-HnSI DmMqC^*uyh<.5P"VrNgD' FROM DUAL UNION
SELECT 19, 'GFmdfx' FROM DUAL UNION
SELECT 20, 'TD0T2A82HNMT1F9AE1 ENDSTRT 6WTZKK34D3ST7X9XL' FROM DUAL UNION
SELECT 21, 'GKH8HASTFBJ5WGFX0 ENDSTRT 4WHDWV3FZQL7' FROM DUAL UNION
SELECT 22, 'VUSNJHOMEV381UIPTBUQBI8JWJ8UPG9' FROM DUAL UNION
SELECT 23, '*1_E.#>s5. ENDSTRT Q6HWxhmMxd' FROM DUAL UNION
SELECT 24, 'RE0H8' FROM DUAL UNION
SELECT 25, 'END STRT' FROM DUAL UNION
SELECT 26, '<_};vH~T#cU~9UxlL^CciGr8K_,_u?)@~qQz6|[]?mgO^4LIif7]m3h@' FROM DUAL UNION
SELECT 27, 'qnFuBdEDLpvWFzJYNDEluWtvcHQFRyYtlRtiLVtyPdheVxKaluwioztSv' FROM DUAL UNION
SELECT 28, 'DJJXX' FROM DUAL UNION
SELECT 29, 'EQUCWchlQQUDWOCMoFyRMgmtBnyqvVSmvqAhDTgMyJDPhyio' FROM DUAL UNION
SELECT 30, 'W6KVN33CV STRT CZZG74FPINO \' FROM DUAL UNION
SELECT 31, 'HRA1NVLENIAV0G' FROM DUAL UNION
SELECT 32, 'U05VZ ENDSTRT' FROM DUAL) SELECT * FROM t WHERE data LIKE '%STRT%';
Expected result:
0 KmBeY STRT s lvl_1.1_s
4 _MKD%Hq,SoojohC(Bg"O}A6THFT STRT s lvl_1.2_s
6 SZGUFLA ENDSTRT RFFVBHKN e lvl_1.2_e
8 STRT s lvl_1.3_s
10 vrDyhnrwpRQOVoFtwyKNDDLc STRT NAENRGCnNiOVJ s lvl_1.4_s
14 iHbtp3/1+=G2,D] STRT W_F$K$F$$jmx;%*W.#[8 s lvl_1.5_s
17 W4SZ7C7WKYKP ENDSTRT e lvl_1.5_e
20 TD0T2A82HNMT1F9AE1 ENDSTRT 6WTZKK34D3ST7X9XL e lvl_1.4_e
21 GKH8HASTFBJ5WGFX0 ENDSTRT 4WHDWV3FZQL7 e lvl_1.3_e
23 *1_E.#>s5. ENDSTRT Q6HWxhmMxd e lvl_1.2_e
25 ENDSTRT e lvl_1.1_e
30 W6KVN33CV STRT CZZG74FPINO \ s lvl_2.1_s
32 U05VZ ENDSTRT e lvl_2.1_e