Re: Behaviour of INSTR

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. :-)

> 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...

Thanks for your response :-)
Vamsi.

> - Show quoted text -
Received on Thu May 10 2007 - 13:20:45 CDT