使用通配符的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。 OKEN将是100％匹配，而OWEN的匹配度较低。

我的当前测试查询如下：

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

这个查询在搜索字符串中使用通配符*时会出现问题（这是相当常见的情况）。

- Florian

---

你能否提供一些将模糊搜索与通配符结合使用的示例呢？例如，您是否正在寻找类似于这样的内容："abc*"可以比"a12"更好地匹配"abDefghijklmnop"。这只会对前三个字符进行模糊匹配 - 星号后面的任何内容都不应计入得分。 - Jon Heller

我已经添加了一个例子。 - Florian

Answer 1

注意在性能方面采用该方法可能会产生的影响。即使它在“功能上”运行良好，但是使用 UTL_MATCH 您只能通过内部表扫描来 过滤 获取的结果。
您可能需要对此类数据建立一个 索引。
前往 Oracle Text，这是 Oracle 的文本索引功能。请记住，它们需要付出一些努力才能发挥作用。

您可以使用模糊匹配操作符 fuzzy，但是请小心处理。大多数 Oracle Text 功能都与语言有关（它们考虑英语字典、德语等）。

例如

-- 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 Query Operators
• catsearch：Oracle Text SQL Statements and Operators

- Antonio

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

所有“like”匹配都有一个负数，其排序的大小为指定字母数。所有不匹配的“like”都有一个非负的排序数字，其大小为百分比差异。在所有情况下，较小的数字表示更好的匹配。

- Bohemian

---

我在问题底部添加了一个示例。 - Florian

为什么 OKEN*IST* 应该匹配 OWEN TRISTAN？是什么逻辑使得 K 匹配 W？我可以理解 O*EN*IST* 的匹配，但当用户指定了 K 时就无法理解。 - Bohemian

因为SMITH和JONES的编辑距离为0％。我想选择排名前10的结果，按编辑距离排序。如果需要更好的理解，请查看我当前的测试查询。 - Florian

目前看起来不错。能否包含一种代表“分数”或编辑距离的值？通过添加索引可以提高性能吗？ - Florian

所以您要将名字和姓氏连接成一个字符串，然后将其与选择输入进行比较？问题在于，用户可以显式地搜索名字、姓氏或两者。 - Florian