带有通配符的Oracle模糊文本搜索

Question

我有一个SAP Oracle数据库，里面装满了客户数据。在我们的自定义CRM中，使用通配符搜索客户是很常见的。除了SAP标准搜索之外，我们还想对与输入的名称相似的名称进行一些模糊文本搜索。目前，我们正在使用UTL_MATCH.EDIT_DISTANCE函数来搜索类似的名称。唯一的缺点是不可能使用某些通配符模式。

有没有可能将通配符与UTL_MATCH.EDIT_DISTANCE函数结合使用，或者有不同(甚至更好)的方法可以做到这一点？

比方说，数据库中有以下名称：

PATRICK NOR
ORVILLE ALEX
OWEN TRISTAN
OKEN TRIST

查询可能看起来像OKEN*IST*，并且应该返回OWEN TRISTAN和OKEN TRISTAN。CCD_ 6将是100%匹配并且CCD_。

我当前的测试查询如下：

SELECT gp.partner, gp.bu_sort1, UTL_MATCH.edit_distance(gp.bu_sort1, ?) as edit_distance, 
FROM but000 gp
WHERE UTL_MATCH.edit_distance(gp.bu_sort1, ?) < 4

除非在搜索字符串中使用通配符*(这很常见)，否则此查询工作正常。

Answer 1

注意您的方法在性能方面的影响。即使它在"功能上"起作用，使用UTL_MATCH，您也只能过滤内部表扫描获得的结果
您可能需要的是此类数据的索引
前往Oracle Text，这是Oracle的文本索引功能。请记住，它们需要付出一些努力才能发挥作用。

您可能需要处理fuzzy运算符，但要小心处理。大多数oracle文本功能都依赖于语言(它们考虑了英语词典、德语等)

例如

-- create and populate the table
create table xxx_names (name varchar2(100));
insert into xxx_names(name) values('PATRICK NOR');
insert into xxx_names(name) values('ORVILLE ALEX');
insert into xxx_names(name) values('OWEN TRISTAN');
insert into xxx_names(name) values('OKEN TRIST');
insert into xxx_names(name) values('OKENOR SAD');
insert into xxx_names(name) values('OKENEAR TRUST');
--create the domain index
create index xxx_names_ctx on xxx_names(name) indextype is ctxsys.context;

这个查询将返回您可能想要的结果(输入是字符串"TRST")

select
SCORE(1), name
from
xxx_names n
where
CONTAINS(n.name, 'definescore(fuzzy(TRST, 1, 6, weight),relevance)', 1) > 0
; 

SCORE(1) NAME               
---------- --------------------
1 OWEN TRISTAN        
22 OKEN TRIST    

但对于输入字符串"IST"，它可能什么都不返回(在我的情况下，它就是这样做的)。还要注意，通常情况下，少于3个字符的输入默认情况下被视为不匹配
如果你去掉"模糊"的要求，坚持寻找只"包含"你传入的确切序列的行，你可能会得到更"可预测"的结果。
在这种情况下，尝试使用ctxcat索引，顺便说一句，它支持一些通配符(警告：支持多列，但列的大小不能超过30个字符！)

-- create and populate the table
--max length is 30 chars, otherwise the catsearch index can't be created
create table xxx_names (name varchar2(30));
insert into xxx_names(name) values('PATRICK NOR');
insert into xxx_names(name) values('ORVILLE ALEX');
insert into xxx_names(name) values('OWEN TRISTAN');
insert into xxx_names(name) values('OKEN TRIST');
insert into xxx_names(name) values('OKENOR SAD');
insert into xxx_names(name) values('OKENEAR TRUST');
begin
ctx_ddl.create_index_set('xxx_names_set');
ctx_ddl.add_index('xxx_names_set', 'name'); 
end;
/
drop index xxx_names_cat;
CREATE INDEX xxx_names_cat ON xxx_names(name) INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('index set xxx_names_set');

后者，使用此查询会很好地工作(输入为"*TRIST*")

select
UTL_MATCH.edit_distance(name, 'TRIST') dist,
name
from
xxx_names
where
catsearch(name, '*TRIST*', 'order by name desc') > 0
;
DIST NAME               
---------- --------------------
7 OWEN TRISTAN        
5 OKEN TRIST      

但是输入"*O*TRIST*"不会返回任何内容(出于某些原因)。

一句话：文本索引可能是(提高性能)的唯一方法，但你必须花很多时间才能理解所有的复杂性。

参考文献：

模糊搜索：Oracle Text CONTAINS查询运算符

catsearch:Oracle文本SQL语句和运算符

Answer 2

假设"通配符"的意思是星号，则希望与所有指定字母匹配的名称排名最高，指定字母越多匹配越好，否则按编辑距离相似性进行排名。

使用占位符?作为搜索词，尝试以下操作：

select *
from mytable
order by case
when name like '%' || replace(?, '*', '%') || '%' then 0 - length(replace(?, '*', ''))
else 100 - UTL_MATCH.edit_distance_similarity(?, name) end
fetch first 10 rows

仅供参考，所有"相似"匹配的排序都有一个负数，大小为指定的字母数。所有类似的未命中都有一个非负序数，其大小为百分比差异。在所有情况下，数字越小匹配越好。