akash February 2016

instr() in oracle 11g

SQL> select instr('stringrings','rin',-4,1) from dual;

INSTR('STRINGRINGS','RIN',-4,1)
-------------------------------
                              7

when i provide the search position as -4 and it searches backward from right to left, it returns the match position as 7 though the whole pattern does not match from the given search starting position. here only the partial pattern matches in the original string.

SQL> select instr('stringrings','rin',-5,1) from dual;

INSTR('STRINGRINGS','RIN',-5,1)
-------------------------------
                              7

here also the same result...

SQL> select instr('stringrings','rin',4,1) from dual;

INSTR('STRINGRINGS','RIN',4,1)
------------------------------
                             7

but here, in positive direction search it ignores the partial pattern match and gives the position where the exact pattern matches.. Can anyone explain it to me? why this double standards?

Answers


hinotf February 2016

No double standarts:

select instr('stringrings','rin',-4,1) from dual;

INSTR start search from stringrings (-4), searching backward until first rin = stringrings (7)

select instr('stringrings','rin',-5,1) from dual;

INSTR start search from stringrings (-5), searching backward until first rin = stringrings (7)

select instr('stringrings','rin',4,1) from dual;

INSTR start search from stringrings (4), searching foreward until first rin = stringrings (7)

Post Status

Asked in February 2016
Viewed 1,780 times
Voted 7
Answered 1 times

Search




Leave an answer