Home » SQL & PL/SQL » SQL & PL/SQL » how to use instr?
how to use instr? [message #423] Mon, 11 February 2002 14:39 Go to next message
Messages: 68
Registered: April 2001
I need to find a substr from a string, the set of substr is revisionNumber, in the string there also is a subtring revision, but I always find the 'revision' instead of 'revisionNumber'. Please help me to fix this. below is my query:

select substr(message, instr(message, 'revisionNumber=', 20, 2)+16, 2) revisionNumber
from ...

the tring ls:

<?xml version="1.0"?><messageEnvelope time="2002/01/23 15:04" messageId="2002012315041925"> <sender>ite</sender> <receiver>iashub.com</receiver> <messageBody> <batch abortOnFailure="false"> <updateEstimate time="2001/09/20 15:04"> <estimate number="USLAXITEX02658" date="2001/09/20 15:04" revisionNumber="2" condition.....

Re: how to use instr? [message #427 is a reply to message #423] Mon, 11 February 2002 19:08 Go to previous message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
substr(message, instr(message, 'revisionNumber=', 20, 2)+16, 2)

Now first we will see how will it work
instr(message, 'revisionNumber=', 20, 2)

here from position of 20 it will start for second occurance of revisionNumber= in message .and it will return a number of second occurance .
if it is 25 .

substr(message, instr(message, 'revisionNumber=', 20, 2)+16, 2)

then it will add 16 in 25 = 41 and returns you the next 2 character .
Previous Topic: stored procedure
Next Topic: how to execute cursors
Goto Forum:

Current Time: Thu Aug 06 02:59:59 CDT 2020