使用通配符和点查询与Oracle Text索引不匹配的数据 [英] Query with wildcard and dot not matching data with Oracle Text index

问题描述

在文本搜索中结合使用通配符和点时，我的查询找不到匹配的行.

When using the wildcard character in combination with a dot in a text search, my query does not find the matching row.

例如:

CREATE TABLE MY_TABLE( ITEM_NUMBER VARCHAR2(50 BYTE) NOT NULL);
INSERT INTO MY_TABLE (ITEM_NUMBER) VALUES ('1234.1234');
create index TIX_ITEMNO on MY_TABLE(ITEM_NUMBER) indextype is ctxsys.context;

我想在MY_TABLE中找到ITEM_NUMBER列为"1234.1234"的行

I want to find the row in MY_TABLE where ITEM_NUMBER column is '1234.1234'

这确实找到了行:

SELECT * FROM MY_TABLE
WHERE CONTAINS(ITEM_NUMBER, '%1234') > 0

这找不到行:

SELECT * FROM MY_TABLE
WHERE CONTAINS(ITEM_NUMBER, '%.1234') > 0

我不明白为什么，因为根据Oracle的说法，点不是必须转义的特殊字符.

I do not understand why, since according to Oracle the dot is not a special character that has to be escaped.

我该如何处理这种情况?

How do I have to handle this situation?

推荐答案

这是因为默认的词法分析器将句点视为单词分隔符.

This is because your default lexer is treating the period as a word separator.

初始设置:

create table my_table(item_number varchar2(50 byte) not null);

insert into my_table values ('1234.1234');

create index my_index on my_table (item_number) 
indextype is ctxsys.context;

这会得到您看到的行为:

This gets the behaviour you see:

SELECT * FROM MY_TABLE
WHERE CONTAINS(ITEM_NUMBER, '%1234') > 0;

--------------------------------------------------
1234.1234

SELECT * FROM MY_TABLE
WHERE CONTAINS(ITEM_NUMBER, '%.1234') > 0;

no rows selected

如果添加定义 PRINTJOINS的词法分析器包括句点:

If you add a lexer that defines PRINTJOINS to include the period:

drop index my_index;

begin 
  ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER'); 
  ctx_ddl.set_attribute('my_lexer', 'PRINTJOINS', '.');
end;
/

create index my_index on my_table (item_number) 
indextype is ctxsys.context
parameters ('lexer my_lexer');

然后按照您想要的方式进行操作:

then it behaves the way you want:

SELECT * FROM MY_TABLE
WHERE CONTAINS(ITEM_NUMBER, '%.1234') > 0;

ITEM_NUMBER
--------------------------------------------------
1234.1234

详细了解文本索引元素.

这篇关于使用通配符和点查询与Oracle Text索引不匹配的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！