I have one clob field 'Class_Data' in my table 'School' and in the clob there is one tag name as '<Fee-Value>' , I want to select the all occurrences of the tag from the clob data. I used the below query in mysql , but it select only the first occurrence of the tag(there are 12 occurrence of the vallue)
Expand|Select|Wrap|Line Numbers
select substr(Class_Data, instr(Class_Data,'Fee-Value'),30) from School where class='S013' and grade='A' and date ='20130301';
That's because instr only finds the first occurrence. You will probably have to use a stored procedure because I can't think of a way to continually iterate through the string and create rows with a standard query.
Without a stored procedure, it's next to impossible and very complex to accomplish. It's nigh impossible unless you know how many are in the string beforehand. And even if you know, it requires many embedded instr calls to pull them all out.