All, Oracle novice here, so bear with me…
I'm trying to understand how Oracle text searching works (contains(myCol, ‘%abc%’) > 0). I've searched the web but can't seem to find ANTHING that will satisfy my inquiry. I'm interested in escaped characters using curly braces. For example: What is the difference among the 3 search queries below:
First, create table and insert values & index:
create table myTABLE(myCOL varchar2(25));
insert into myTABLE(myCOL ) values ('def');
insert into myTABLE(myCOL ) values ('cdef');
insert into myTABLE(myCOL ) values ('abcdefg');
create index myINDEX on myTABLE(myCOL )
indextype is CTXSYS.CONTEXT
- select * from myTABLE where CONTAINS(myCOL , '%def%') > 0;
- select * from myTABLE where CONTAINS(myCOL , '%{def}%') > 0;
- select * from myTABLE where CONTAINS(myCOL , '{%def%}') > 0;
From my view, #1 appears to be a legitimate wildcard situation (results will be “def”, “cdef”, “abcdefg”). #2 would appear to be identical to #1 (there are no special characters or operators to escape, therefore it should read similar to #1 and return the same results). For #3, Oracle would ignore the percent signs and the results would be “def” only (which brings up the question: If the ‘%’ signs are inactive then the query should be looking for the literal string ‘%def%’, which should return nothing).
What am i missing here? Thanks for any help.