I have sample data as follows
[CC|ABCDEFG/RRRRR|SAMPLE1]
[DDDD|RR CAT REW RIGHT|SAMPLE2]
I need to separate the text after [ and before 1st occurrence of | -
[CC|ABCDEFG/RRRRR|SAMPLE1] --> gives CC
[DDDD|RR CAT REW RIGHT|SAMPLE2] --> gives DDDD
I also have to separate the text between the 1st and 2nd occurance of | --> ABCDEFG/RRRR and RR CAT REW RIGHT
I am able to achieve that using below, but how do I get the text before the first occurance of | and ignoring the [ --> Meaning from above, CC and DDDD
select REGEXP_SUBSTR ('[CC|ABCDEFG/RRRRR|SAMPLE1]',
'\|(.+)\|',
1,
1,
NULL,
1) from dual