We help IT Professionals succeed at work.
Get Started

Oracle - subst Instr

Putoch asked
Last Modified: 2015-03-04
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>
<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.

  instr(FIELDNAME,'<In/><Out><Param Name="Message" Value="',1,1) +LENGTH('<In/><Out><Param Name="Message" Value="'),
   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,
Watch Question
Most Valuable Expert 2012
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE