Hi,
I have a large CLOB datatype field I'm working with.
Within it I am trying to pull back some information
Each of the entries within this CLOB will be quite different but I do have and end and start point between the piece of data that I want to pull out of this field.
I have been trying to do a SUBST / INSTR combo but I can't get this to return the results I wanted, most times it returns just blanks. If someone can help I'd really appreciate this.
Example of the field would be:
<Params><In/><Out><Param Name="Message" Value="This is the piece I need."/></Out></Params>
Or
<Params><In><Param Name="PlanID" Value="123546"/></In><Out><Param Name="ResultMessage" Value="This is the piece I need."/></Out></Params>
Lets call the Field FIELDNAME
You can see the piece I want to pull is the bit in quotes "This is the piece I need"
Each string has the exact section at the start and end between the string I need. The sting I need can vary in length.
I'm trying to understand how I can , with out adding the number so its more dynamic depending on the length of the string, could I add something else to calculate that value.
and without using replace to remove the end part of the field - could I use something else.
SELECT
REPLACE( substr(FIELDNAME,
instr(FIELDNAME,'<In/><Out><Param Name="Message" Value="',1,1) +LENGTH('<In/><Out><Param Name="Message" Value="'),
LENGTH(E1.HEVT_PARAMETERS) -
instr(E1.HEVT_PARAMETERS,'<In/><Out><Param Name="ResultMessage" Value="',1,1),
"/></Out></Params>','') )
My method works for the first field example, but not the second and im not sure why.
any advice is much appreciated
Thank you,