使用花括号和通配符转义Oracle文本 [英] Oracle text escaping with curly braces and wildcards

问题描述

我希望能够使用contains对Oracle文本查询中的搜索条件进行转义，并将转义的条件与通配符组合在一起，以具有双重截断"的条件. (我知道我的索引可能没有设置为理想的性能，但这是多余的).我希望能够使用花括号语法来获得最佳的可读性，但这不起作用.根据这个相关问题(但不是重复问题)的最高答案，花括号定义了完整的标记.有什么办法可以禁用或解决此问题?

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:如何清理用户输入

由于保留字也被认为是特殊"字词，因此我宁愿避免在搜索条件中转义每个字符(按照代码中的最后选择)或尝试在字符串中搜索特殊字符. (请注意，我没有停用词)以下内容说明了我的问题. (不幸的是，SQLFiddle似乎不支持Oracle文本):

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

推荐答案

怎么样:

select * from MY_TABLE where CONTAINS(MY_COL, regexp_replace('*abc*', '([^*])', '\\\1')) > 0

这篇关于使用花括号和通配符转义Oracle文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！