Oracle fuzzy text search with wildcards

Question

I've got a SAP Oracle database full with customer data. In our custom CRM it is quite common to search the for customers using wildcards. In addtion to the SAP standard search, we would like to do some fuzzy text searching for names which are similar to the entered name. Currently we're using the UTL_MATCH.EDIT_DISTANCE function to search for similar names. The only disadvantage is that it is not possible to use some wildcard patterns.

Is there any possiblity to use wildcards in combination with the UTL_MATCH.EDIT_DISTANCE function or are there different(or even better) approaches to do that?

Let's say, there are the following names in the database:

PATRICK NOR
ORVILLE ALEX
OWEN TRISTAN
OKEN TRIST

The query could look like OKEN*IST* and both OWEN TRISTAN and OKEN TRISTAN should be returned. OKEN would be a 100% match and OWEN less.

My current test-query looks like:

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

This query works fine except if wildcards * are used within the search string (which is quite common).

Answer 1

Beware of the implications of your approach in terms of performances. Even if it "functionally" worked, with UTL_MATCH you can only filter the results obtained by an internal table scan.
What you likely need is an index on such data.
Head to Oracle Text, the text indexing capabilities of Oracle. Bear in mind that they require some effort to be put at work.

You might juggle with the fuzzy operator, but handle with care. Most oracle text features are language dependent (they take into account the English dictionary, German, etc..).

For instance

-- 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;

This query would return results that you'd probably like (input is the string "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    

But with the input string "IST" it would likely return nothing (in my case this is what it does). Also note that in general, inputs of less than 3 characters are considered non-matching by default.
You'll possibly get a more "predictable" outcome if you take off the "fuzzy" requirement and stick to finding rows that just "contains" the exact sequence you passed in.
In this case try using a ctxcat index, which, by the way supports some wildcards (warning: supports multi columns, but a column cannot exceed 30 chars in size!)

-- 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');

The latter, with this query would work nicely (input is "*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      

But with the input "*O*TRIST*" wouldn't return anything (for some reasons).

Bottom line: text indexes are probably the only way to go (for performance) but you have to fiddle quite a bit to understand all the intricacies.

References:

• fuzzy search: Oracle Text CONTAINS Query Operators
• catsearch : Oracle Text SQL Statements and Operators

Answer 2

Assuming "wildcard" means an asterisk, you want a name that matches all specified letters to rank highest, with more specified letters matching better than less, otherwise rank by edit distance similarity.

using the placeholder ? for your search term, try this:

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

FYI all "like" matches have a negative number for their ordering with magnitude the number of letters specified. All like misses have a non-negative ordering number with magnitude of the percentage difference. In all cases, a lower number is a better match.