SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

extract substring from a main string in clob datatype

user8195117Feb 22 2022

Hi All,
I have the sample data stored in clob column in oracle table and would like to extract a substring from the main string either through the occurrence of the string or based on line number.
HH1*AA1*BB1*CC1*DD1*EE1*
............
.........
...........
HH1*AA2*BB2*CC2*DD2*EE3*
.........
........
..........
HH1*AA1*BB1*CC1*DD1*EE1*FF2
..........
.............
.......
HH1*AA1*BB1*CC1*DD1*EE1*FF2
........
..........
.........
.........
.........
........
HH1*AA1*BB1*CC1*DD1*EE1*FF2
..............
............
...........
...........
HH1*AA1*BB1*CC1*DD1*EE1*FF2
.............
.........
..........
HH1*AA1*BB1*CC1*DD1*EE1*FF2
...........
...........
..........
..........
HH1*AA1*BB1*CC1*DD1*EE1*FF2
.............
..............
.............
HH1*AA1*BB1*CC1*DD1*EE1*
...............
.............
HH1*AA1*BB1*CC1*DD1*EE1*FF2
....
....
....
The above string has data content with 10 occurrences of HH1* and would like get the desired output in chunks i.e. 2 HH1* in each substring
Expected o/p in chunks required
O/p Set 1:HH1*AA1*BB1*CC1*DD1*EE1*
............
.........
...........
HH1*AA2*BB2*CC2*DD2*EE3*
.........
........
..........
Set2: HH1*AA1*BB1*CC1*DD1*EE1*FF2
..........
.............
.......
HH1*AA1*BB1*CC1*DD1*EE1*FF2
........
..........
.........
.........
.........
........ and so on.

So Each set should be 2 sets of HH1. Can this be done in plsql in clob column through line number or use dbms_lob.substr.

Oracle 12g

Added on Feb 22 2022

21 comments

6,538 views

4 mentions