2

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

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;

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.

Kuldeep
  • 599
  • 11
  • 28

1 Answers1

2

First please check the documentation of the Fuzzy operator

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.

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

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 enforces here that both matches must be adjacent (span = 2) and ordered (TRUE).

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

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53