Behaviour of INSTR

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. Received on Tue May 08 2007 - 17:29:18 CDT