I want to be able to escape the search criteria in an Oracle text query using contains and combine the escaped criteria with wildcards to have "doubly truncated" criteria. (I know my indexes may not be setup for ideal performance, but that is superfluous). I want to be able to use the curly braces syntax for best readability but this doesn't work. According to the top answer on this related (but not duplicate) question, curly braces define complete tokens. Is there any way to disable or work around this behavior?
Oracle Text: How to sanitize user input
I would rather avoid having to escape every single character in my search criteria (as per the last select in my code) or try to search the string for special characters since reserved words are also considered "special". (Note that I have no stop words) The following demonstrates my problem. (Unfortunately SQLFiddle does not appear to support Oracle text):
create table MY_TABLE(MY_COL varchar2(20));
insert into MY_TABLE(MY_COL) values ('abc');
insert into MY_TABLE(MY_COL) values ('abcd');
insert into MY_TABLE(MY_COL) values ('abcde');
insert into MY_TABLE(MY_COL) values ('bcd');
insert into MY_TABLE(MY_COL) values ('bcde');
create index FTIX on MY_TABLE (MY_COL)
indextype is CTXSYS.CONTEXT
PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST SYNC (ON COMMIT)');
select * from MY_TABLE where CONTAINS(MY_COL, '%bcd%') > 0; --expected results
select * from MY_TABLE where CONTAINS(MY_COL, '%{bcd}%') > 0; --no results
select * from MY_TABLE where CONTAINS(MY_COL, '{bcd}') > 0; --returns bcd
select * from MY_TABLE where CONTAINS(MY_COL, '{%bcd%}') > 0; --returns bcd
select * from MY_TABLE where CONTAINS(MY_COL, '%\b\c\d%') > 0; --expected results
How about:
select * from MY_TABLE where CONTAINS(MY_COL, regexp_replace('*abc*', '([^*])', '\\\1')) > 0