Re: Behaviour of INSTR

From: <fitzjarrell_at_cox.net>
Date: 10 May 2007 11:49:57 -0700
Message-ID: <1178822996.882375.186510@y5g2000hsa.googlegroups.com>

On May 10, 1:20 pm, tvamsid..._at_gmail.com wrote:
> On May 8, 9:02 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
> > On May 8, 6:29 pm, tvamsid..._at_gmail.com wrote:
>
> > > Hello,
>
> > > I noticed results I wasn't expecting from the InStr function. Thought
> > > I'd ask if my expectations are reasonable.
>
> > > SQL> select instr('A---B---C------E', '---', 1, 3) as pos from dual;
>
> > > POS
> > > ----------
> > > 10
>
> > > SQL> select instr('A---B---C------E', '---', 1, 4) as pos from dual;
>
> > > POS
> > > ----------
> > > 11
>
> > > I was expecting the second statement (the last parameter==4) to return
> > > 13 instead of 11.
>
> > > My reason: the function should skip over (4-1) *full* insances of
> > > '---' and find the first one *after* that, not after the first
> > > character of the instance number (4-1)
>
> > > Its simple enough to write a function of my own to do this, but, is
> > > there a built-in function that can do this?
>
> > > Thanks.
> > > Vamsi.
>
> > It works the way it is documented (http://tahiti.oracle.com) ... if
> > you find and produce a reproducible test case that doesn't do what it
> > should you can file a bug.
>
> > Have you looked at the documentation for it?
>
> Yes, but, I think I interpreted the meaning of the 4th parameter
> (occurance)
> Given a string 'xxxx', and looking for occurances of the string 'xx',
> I thought there are *2* (at positions 1 and 3) but,
> it turns out there are *3* (at positions 1, 2 and 3)
>
> Given the context (finding occuerance of a given string) I still think
> that once an occurance has been found, the search for the next
> occurance shpuld begin after the current occurance has ended. :-)

Why? Oracle is doing this properly by 'walking' the string and finding matches at a starting point immediately after the reported start of the last occurrence:

SQL> select instr('xxxxxxxxxx', 'xxx', 1) from dual;

INSTR('XXXXXXXXXX','XXX',1)

---

                          1

SQL> c/1/2
  1* select instr('xxxxxxxxxx', 'xxx', 2) from dual SQL> / INSTR('XXXXXXXXXX','XXX',2)

---

                          2

SQL> c/2/3
  1* select instr('xxxxxxxxxx', 'xxx', 3) from dual SQL> / INSTR('XXXXXXXXXX','XXX',3)

---

                          3

 SQL> c/3/4
  1* select instr('xxxxxxxxxx', 'xxx', 4) from dual SQL> / INSTR('XXXXXXXXXX','XXX',4)

---

                          4

SQL> c/4/5
  1* select instr('xxxxxxxxxx', 'xxx', 5) from dual SQL> / INSTR('XXXXXXXXXX','XXX',5)

---

                          5

SQL> c/5/6
  1* select instr('xxxxxxxxxx', 'xxx', 6) from dual SQL> / INSTR('XXXXXXXXXX','XXX',6)

---

                          6

SQL> c/6/7
  1* select instr('xxxxxxxxxx', 'xxx', 7) from dual SQL> / INSTR('XXXXXXXXXX','XXX',7)

---

                          7

SQL> c/7/8
  1* select instr('xxxxxxxxxx', 'xxx', 8) from dual SQL> / INSTR('XXXXXXXXXX','XXX',8)

---

                          8

SQL> c/8/9
  1* select instr('xxxxxxxxxx', 'xxx', 9) from dual SQL> / INSTR('XXXXXXXXXX','XXX',9)

---

                          0

SQL>
>
> > What exactly are you trying to accomplish in your application? Newer
> > versions of oracle support regular expressions.- Hide quoted text -
>
> I'm writing a function that can "split" a string (which is treated as
> a sting of values separated by a delimiter that is passed as a
> parameter) I saw this problem when I had to use a delimiter like '---'
> and once of the values was empty...

Then you code for that eventuality by checking for instr() to return a value 1 greater than your prior value; this would indicate a NULL value in your delimited string and you'd process accordingly.

>
> Thanks for your response :-)
> Vamsi.
>
>
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Thu May 10 2007 - 13:49:57 CDT