CODEWITHSUNDEEP
oracle-databaseoracle11g
akash
akash

Reputation: 1

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?

Upvotes: 0

Views: 110

Answers (1)

hinotf
hinotf

Reputation: 1138

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)

Upvotes: 2

Related Questions