带空格的 Oracle 模糊搜索 [英] Oracle Fuzzy Search with spaces

问题描述

我想在 Oracle 中进行模糊搜索，但查询无法使用空格.

I want to do fuzzy search in Oracle but query is not working with spaces.

例如.假设我们有一个表 po_test_tmp 有很多记录

ex. Let say we have a table po_test_tmp which has many records

如果我们执行这个查询它工作正常

If we execute this query it works fine

select score(1), ae.po_number
from po_test_tmp ae
where CONTAINS(po_number, 'fuzzy(po, 50,5000, weight)', 1)> 0
order by score(1) desc;

但是如果我们尝试搜索像 PO 123 这样的词，它就行不通了

But if we try to search term like PO 123 it with not work

select score(1), ae.po_number
from po_test_tmp ae
where CONTAINS(po_number, 'fuzzy(PO 123, 50,5000, weight)', 1)> 0
order by score(1) desc;

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 10  
29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

如果搜索词有空格怎么做模糊搜索，搜索词可以有特殊字符和多个空格.

How to do fuzzy search if search term has spaces, and search term can have special character and multiple spaces.

推荐答案

首先请查看模糊运算符

参数term - 指定要对其执行模糊扩展的词.

Parameter term - Specify the word on which to perform the fuzzy expansion.

请注意，词通常不包含空格，您必须将搜索拆分为搜索词.

Note that a term typically does not contain blank and you'll have to split your search in searate terms.

另外注意fuzzy要激活，词条必须至少3个字符.因此，您使用 PO 的示例将不起作用.

Additionally note that fuzzy to be activated, the term must have at least 3 characters. So your example with PO will not work.

您可以为您的设置做的是为模糊字符串的每个部分定义一个额外的fuzzy 搜索并将它们与near操作符如下

What you can do for your setup is to define an extra fuzzy search for each part of your fuzzy string and combine them with the near operator such as follows

 contains(po_number,'near (( fuzzy(word1,,,weight),fuzzy(word2,,,weight) ), 2, TRUE)',1) > 0

near 在这里强制要求两个匹配项必须相邻 (span = 2) 且有序 (TRUE).

near enforces here that both matches must be adjacent (span = 2) and ordered (TRUE).

特殊字符的处理取决于您的首选项中whitespace和printjoins的定义.

The handling of special characters depends on the definition of your whitespace and printjoins in your preference.

这篇关于带空格的 Oracle 模糊搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！